29 March 2009 12:19
tonyrogerson
Creating an output CSV using FOR XML and multiple rows
Ok, so I’ve done this one a few times the other way in but what about if you need to create a result set like below…
Source Data
| grp | cval |
| 1 | 1 |
| 1 | 2 |
| 1 | 3 |
| 1 | 4 |
| 1 | 5 |
| 2 | 5 |
| 2 | 6 |
| 2 | 7 |
| 2 | 8 |
| 2 | 4 |
| 3 | 6 |
| 3 | 5 |
| 3 | 3 |
| 3 | 2 |
| 3 | 4 |
Result Required
| grp | csv |
| 1 | 1,2,3,4,5 |
| 2 | 5,6,7,8,4 |
| 3 | 6,5,3,2,4 |
FOR XML only gets you so far, well – 1 row to be exact, so how do we break it out for the multiple rows per grp?
The trick is to use a sub-query on the SELECT and wrap your FOR XML logic into that…
declare @tb table (
agrp int,
aval int
)
insert @tb values( 1, 1 )
insert @tb values( 1, 2 )
insert @tb values( 1, 3 )
insert @tb values( 2, 1 )
insert @tb values( 2, 2 )
insert @tb values( 2, 3 )
insert @tb values( 2, 4 )
select r.agrp,
collapsed = left( r.collapsed, len( r.collapsed ) - 1 )
from (
select a.*,
collapsed = ( select cast( aval as varchar(max) ) + ',' as [text()]
from @tb b
where b.agrp = a.agrp
for xml path( '' )
)
from (
select distinct agrp
from @tb ) as a
) as r
Filed under: SQL Server, SQL Development