Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations strongm on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Concatenation of multiple rows into a single row (sybase)

Status
Not open for further replies.

maddyforums

Programmer
May 29, 2008
3
US
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
 
Might be able to do it with a self join or case statement if the max seq_no is small
 
Thanks PDreyer, the max seq_no is 2. Can you please send me the query for this ?
 
Code:
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
 
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
 
You'll have to build the query in your code depending on max(Seq_no)
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top