SYBASE: Merge multiple rows into 1 record with comma separated field

CREATE TABLE #tbl (ad_id INT, ac_id VARCHAR(255), dt INT, rel INT )

INSERT INTO #tbl VALUES (1,'10',111,1)

INSERT INTO #tbl VALUES (2,'11',112,0)

INSERT INTO #tbl VALUES (3,'12',113,0)

INSERT INTO #tbl VALUES (3,'13',114,1)

INSERT INTO #tbl VALUES (3,'14',115,1)

INSERT INTO #tbl VALUES (4,'15',116,1)

INSERT INTO #tbl VALUES (4,'16',117,1)

INSERT INTO #tbl VALUES (4,'17',118,1)

INSERT INTO #tbl VALUES (4,'18',121,1)

INSERT INTO #tbl VALUES (4,'19',119,1)

INSERT INTO #tbl VALUES (4,'20',120,1)

SELECT * , dt1 = MIN(dt)

INTO #tbl2

FROM #tbl

WHERE rel = 1

GROUP BY ad_id

HAVING rel = 1

ORDER BY ad_id, dt

DECLARE @LastDesc VARCHAR(512)

SELECT @LastDesc = ''

UPDATE #tbl2

SET @LastDesc = CASE WHEN dt = dt1

THEN ac_id

ELSE @LastDesc +','+ac_id END,

ac_id = CASE WHEN dt = dt1 THEN ac_id

ELSE @LastDesc +','+ac_id END

SELECT *

FROM #tbl2

GROUP BY ad_id HAVING dt = max(dt)

SYBASE: CONSOLIDATE Multiple rows into comma separated field

CREATE TABLE #tbl (ad_id INT, ac_id VARCHAR(255), dt INT, rel INT)

INSERT INTO #tbl VALUES (1,'10',111,1)

INSERT INTO #tbl VALUES (2,'11',112,0)

INSERT INTO #tbl VALUES (3,'12',113,0)

INSERT INTO #tbl VALUES (3,'13',114,1)

INSERT INTO #tbl VALUES (3,'14',115,1)

INSERT INTO #tbl VALUES (4,'15',116,1)

INSERT INTO #tbl VALUES (4,'16',117,1)

INSERT INTO #tbl VALUES (4,'17',118,1)

SELECT * , dt1 = MIN(dt), flg = 0

INTO #tbl2

FROM #tbl

WHERE rel = 1

GROUP BY ad_id

HAVING rel = 1

UPDATE #tbl2

        SET flg = 1

    FROM #tbl2

    WHERE dt = dt1

WHILE EXISTS (SELECT 1 FROM #tbl2

            WHERE  flg = 0)

BEGIN

    UPDATE #tbl2

            SET ac_id = t1.ac_id +',' + t2.ac_id

                ,flg = 2

        FROM #tbl2 t1, #tbl2 t2

        WHERE t1.ad_id = t2.ad_id

        AND t1.dt = t1.dt1

        AND t2.dt = (SELECT MIN(dt) FROM #tbl2

                        WHERE ad_id = t1.ad_id

                        AND dt > t1.dt)

    DELETE #tbl2

        FROM #tbl2 t1

        WHERE t1.dt = (SELECT MIN(dt) FROM #tbl2

                        WHERE ad_id = t1.ad_id

                        AND dt > t1.dt1)

END           

SELECT * FROM #tbl2

SYBASE: CONSLIDATE Mutliple ROWS INTO SINGLE Field with comma separated another logic

create table test1

(

col1 int,

col2 int,

col3 varchar(255) null,

flg  char(1) default  'N'

)

INSERT INTO test1 (col1,col2) values(1,2)

INSERT INTO test1 (col1,col2) values(1,3)

INSERT INTO test1 (col1,col2) values(2,4)

INSERT INTO test1 (col1,col2) values(2,5)

select * from test1

declare @col1 int

declare @col2 int

declare @counter int

set @counter=0

while ((select count(*) from test1 WHERE flg='N') >1)

begin

set rowcount 1

select @col1=col1,@col2=col2 from test1 WHERE flg='N'

select @col1,@col2

if (@counter=0)

begin

print 'inside counter 0'

update test1 set col3 = convert(varchar,@col2),flg='Y'

from test1

where col1=@col1

and col2=@col2

AND flg='N'

AND col3 is null

set @counter=1

end

select @col2=col2 from test1 WHERE flg='N' AND col1=@col1

if(@counter =1)

begin

print 'inside counter 1'

update test1 set col3 = convert(varchar,col2) + ','+convert(varchar,@col2),flg='Y'

from test1

where col1=@col1

AND flg='N'

AND col3 is not null

end

UPDATE test1 SET flg='Y'

where col1=@col1 and col2=@col2

set rowcount 0

end

select * from test1

ENGLISH DICTIONARY: LIAISON

1.   li·ai·son

ˈlēəˌzänlēˈā-

noun

noun: liaison

1.   1.

communication or cooperation that facilitates a close working relationship between people or organizations.

"the head porter works in close liaison with the reception office"

§  a person who acts as a link to assist communication or cooperation between groups of people.

plural noun: liaisons

"he's our liaison with a number of interested parties"

synonyms:

intermediary, mediator, middleman, contact, link, connection, go-between, representative, agent More "Dave was my liaison with the district manager"

§  a sexual relationship, esp. one that is secret and involves unfaithfulness to a partner.

synonyms:

love affair, affair, relationship, romance, attachment, fling, amour, romantic entanglement, entanglement, tryst; More informalhanky-panky, dirty weekend "a secret liaison"

2.   2.

the binding or thickening agent of a sauce, often based on egg yolks.

Example: VANWILDER: THE PARTY LIAISON         

SYBASE: DATEADD FUNCTION

dateadd

Description

Returns the date produced by adding or subtracting a given number of years, quarters, hours, or other date parts to the specified date.

Syntax

dateadd(date_part, integer, date expression)

Parameters

date_part

is a date part or abbreviation. For a list of the date parts and abbreviations recognized by Adaptive Server, see “Date parts”.

numeric

is an integer expression.

date expression

is an expression of type datetime, smalldatetime, date, time, or a character string in a datetime format.

Examples

Example 1

Displays the new publication dates when the publication dates of all the books in the titles table slip by 21 days:

select newpubdate = dateadd(day, 21, pubdate) 

from titles 

Example 2

Add one day to a date:

declare @a date

select @a = "apr 12, 9999"

select dateadd(dd, 1, @a)

--------------------------

Apr 13 9999

Example 3

Subtracts five minutes to a time:

select dateadd(mi, -5, convert(time, "14:20:00"))

--------------------------

2:15PM

Example 4

Add one day to a time and the time remains the same:

declare @a time

select @a = "14:20:00"

select dateadd(dd, 1, @a)

--------------------------

2:20PM

Example 5

Although there are limits for each date_part, as with datetime values, you can add higher values resulting in the values rolling over to the next significant field:

--Add 24 hours to a datetime

select dateadd(hh, 24, "4/1/1979")

-------------------------- 

Apr  2 1979 12:00AM 

--Add 24 hours to a date

select dateadd(hh, 24, "4/1/1979")

------------------------- 

Apr  2 1979 

Usage

·         dateadd, a date function, adds an interval to a specified date. For more information about date functions, see “Date functions”.

·         dateadd takes three arguments: the date part, a number, and a date. The result is a datetime value equal to the date plus the number of date parts.

If the date argument is a smalldatetime value, the result is also a smalldatetime. You can use dateadd to add seconds or milliseconds to a smalldatetime, but such an addition is meaningful only if the result date returned by dateadd changes by at least one minute.

·         Use the datetime datatype only for dates after January 1, 1753. datetime values must be enclosed in single or double quotes. Use the date datatype for dates from January 1, 0001 to 9999. date must be enclosed in single or double quotes.Use char, nchar, varchar, or nvarchar for earlier dates. Adaptive Server recognizes a wide variety of date formats. For more information, see “User-defined datatypes” and “Datatype conversion functions”.

Adaptive Server automatically converts between character and datetime values when necessary (for example, when you compare a character value to a datetime value).

·         Using the date part weekday or dw with dateadd is not logical, and produces spurious results. Use day or dd instead.

Table 2-6: date_part recognized abbreviations
Date part	Abbreviation	Values
Year	yy	1753 – 9999 (datetime) 1900 – 2079 (smalldatetime) 0001 – 9999 (date)
Quarter	qq	1 – 4
Month	mm	1 – 12
Week	wk	1054
Day	dd	1 – 7
dayofyear	dy	1 – 366
Weekday	dw	1 – 7
Hour	hh	0 – 23
Minute	mi	0 – 59
Second	ss	0 – 59
millisecond	ms	0 – 999

Standards

ANSI SQL – Compliance level: Transact-SQL extension.

Permissions

Any user can execute dateadd.