• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 424
  • Last Modified:

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,
0
ivan_belal
Asked:
ivan_belal
  • 2
1 Solution
 
lwadwellCommented:
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

0
 
ivan_belalAuthor Commented:
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
0
 
lwadwellCommented:
;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 char(10) + Data FROM sample_data b WHERE a.ukey = b.ukey ORDER BY Sequence FOR XML PATH ('')), 1, 1, '') as all_data 
      from sample_data a)v
group by ukey, all_data

Open in new window

0

Featured Post

2018 Annual Membership Survey

Here at Experts Exchange, we strive to give members the best experience. Help us improve the site by taking this survey today! (Bonus: Be entered to win a great tech prize for participating!)

  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now