Link to home
Start Free TrialLog in
Avatar of ivan_belal
ivan_belal

asked on

String rows into column - help.

Hi, Experts:
I have a table as:

Ukey,Sequence,Data
1,1,note1
1,2,note2
1,3,note3

Sequence is not fixed, but random. Records are joined with Ukey.
I need to produce one row per Ukey as:
1,1,note1 [newline] note2 [newline] note3

Note: Sequence 1 is updated. How can I do this in Sql 2005.

Thanks,
Avatar of Lee Wadwell
Lee Wadwell
Flag of Australia image

Did you want something like below?  By [newline] I assumed you meant a line feed (ascii 10) character.

;with sample_data as (
select 1 Ukey, 1 Sequence, 'note1' Data UNION ALL
select 1 Ukey, 2 Sequence, 'note2' Data UNION ALL
select 1 Ukey, 3 Sequence, 'note3' Data UNION ALL
select 2 Ukey, 1 Sequence, 'more1' Data UNION ALL
select 2 Ukey, 2 Sequence, 'more2' Data)
--
--
select cast(ukey as varchar)+','+cast(min(Sequence) as varchar)+','+all_data as one_line
from (select ukey, Sequence
           , stuff((SELECT DISTINCT char(10) + Data FROM sample_data b WHERE a.ukey = b.ukey FOR XML PATH ('')), 1, 1, '') as all_data 
      from sample_data a)v
group by ukey, all_data

Open in new window

Avatar of ivan_belal
ivan_belal

ASKER

This is good; however I need to order by sequence so, data are placed sequencially.
Now I get note1 note3 then note2 for example.

Can you plz update your query ? Thx
ASKER CERTIFIED SOLUTION
Avatar of Lee Wadwell
Lee Wadwell
Flag of Australia image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial