You might have heard about people looking for concatenating more
than one rows into a csv string. Previously i found out difficult
enough. I used coaelesce to do that . But coaelesce usually asks for a
varibale to be declared and updates the values in that variable on an
iterative basis. But then i tried using XML. It worked pretty well with
that using a single query. Most of you might be aware of this, but let
me share it for the other who are novice to this.
Say we have a table which has two columns as described in the defination below:
Declare @tbl table (id int,sid int)
id
is a primary id and sid is a secondary id which can have multiple
values for the same. The idea is to concatenate all the sids of a
single id into a single row delimited by comma.
Lets fill the table with some values
insert into @tbl
select 1,11
union
select 1,12
union
select 1,13
union
select 2,21
union
select 2,22
This will insert few rows into the table. Now our first task would be to get all the sids of a single id.
Below query would hep you getting the same:
select distinct id,
(select cast(sid as varchar)+',' from @tbl t where t.id=t1.id for XML path('')) as sid
from @tbl t1
The inner query selected as the second column is using XML to get a comma separated list of rows.
id sid
1 11,12,13,
2 21,22,
Now, to remove the last comma we can have a replace function as shown:
select distinct id,
replace((select cast(sid as varchar)+',' from @tbl t where t.id=t1.id for XML path(''))+'$',',$','') as sid
from @tbl t1
id sid
1 11,12,13
2 21,22
I have appended a '$' at the end to identify as the last comma only has to be replaced.
Cool no, see how easily we can create a comma delimited string.
Please do let me know any feed backs.
Tags: XML, multiple_rows, Concatenate,