SQL To Concatenate fields

[jconway]

I am trying to concatenate the last 5 notes on each account in my database into one string field.

My DB looks like:

Acct# Note RowNum
1 Note 1 1
1 Note 2 2
1 Note 3 3
2 2 Note 1 1
2 2 Note 2 2
3 3 Note 1 1
4 4 Note 1 1
4 4 Note 2 2

What I need for output is:

Acct# Notes
1 Note 1 Note 2 Note 3
2 2 Note 1 2 Note 2
3 3 Note 1
4 4 Note 1 4 Note 2

I would appreciate any suggestions on how to do this. Thank you!

 

[gmmastros]

Take a look here: thread183-1159740

-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[markros]

Check links from

http://forums.asp.net/p/1391544/2975969.aspx#2975969

(Took me a while to find this topic).

 

[RyanEK]

Hi,

If you're using SQL2005 and above you can do it in one query using xml path

declare @temp table (acct int, note varchar(10), rownum int)
insert into @temp values (1,'Note 1',1)
insert into @temp values (1,'Note 2',2)
insert into @temp values (1,'Note 3',3)
insert into @temp values (2,'2 Note 1',1)
insert into @temp values (2,'2 Note 2',2)
insert into @temp values (3,'3 Note 1',1)
insert into @temp values (4,'4 Note 2',1)
insert into @temp values (4,'4 Note 3',2)

select acct,
replace ((
  select note as 'data()'
  from @temp t2
  where t1.acct = t2.acct
  order by t2.rownum
  for xml path('')),' ',' ') as note
from @temp t1
group by acct

Ryan

 

[markros]

See also

http://social.msdn.microsoft.com/Fo...l/thread/b64f2f24-d9d5-41f9-9f26-eced21fa70e1

and this link from it

http://www.projectdmx.com/tsql/rowconcatenate.aspx