Jan 3, 2008

using FOR XMLPATH to concat multiple row values

The need is to concatenate multiple row values from a table
The following TSQL in SQL 2005 can assist in returning values

Following is the input table
ID Value
---------
1 '-'
1 'C'
1 'B'
1 'A'
2 '='
2 'G'
2 'D'
=======
Expected output :
ID Concat
1 '-CBA'
2 '=GD'
=================

TSQL for this :
select Distinct ID, o.List
from
#t1 A
CROSS APPLY
(
select Val as [text()] from #t1 B
where B.ID = A.ID
FOR XML PATH('')
) o (List)

kick it on DotNetKicks.com

No comments: