Concatenation of multiple rows into a single row (sybase)

[maddyforums]

Can someone please help me out in writing the Sql to concatenate the Text_desc for each code for all the seq_nos in the ascending order of seq_no and load into the target table.

Source table : S1

Code Seq_no Text_desc
---------------------------
1579 0 BCN5, CO20, ER75
1579 1 FP5, WC6, MATW, OPRH
1579 2 T
1600 0 TW, FP5, AS5, MHSA15, DME20
1600 1 ER100, UR40

Target table: T1

Text_desc (concatenate all the strings for each code in ascending order of seq_no)

code Text_desc
----- ----------------
1579 BCN5, CO20, ER75FP5, WC6, MATW, OPRHT
1600 TW, FP5, AS5, MHSA15, DME20ER100, UR40


Thanks,
Maddy

 

[PDreyer]

Might be able to do it with a self join or case statement if the max seq_no is small

 

[maddyforums]

Thanks PDreyer, the max seq_no is 2. Can you please send me the query for this ?

 

[PDreyer]

insert into t1
select Code
, min(case Seq_no when 0 then Text_desc end)
+ min(case Seq_no when 1 then ', '+Text_desc end)
+ min(case Seq_no when 2 then ', '+Text_desc end)
from ( select -- test data --
1579,     0, 'BCN5, CO20, ER75'            union all select
1579,     1, 'FP5, WC6, MATW, OPRH'        union all select
1579,     2, 'T'                           union all select
1600,     0, 'TW, FP5, AS5, MHSA15, DME20' union all select
1600,     1, 'ER100, UR40'                 )s1(
---- ------  ---------- 
Code,Seq_no, Text_desc)
group by Code

 

[maddyforums]

Thanks a lot for sending me the query.
I have 232000 records in the S1 table with similar type of data and also found that the Seq_no can be more even though the max seq_no is 2 at present.
Is it possible to generalize this query? What is the complexity involved?

Thanks
Maddy

 

[PDreyer]

You'll have to build the query in your code depending on max(Seq_no)