I have a table with values:
Key1 Key2 ColumnKey
============================
1 idx1 here
2 idx2 there
I need to return, for more than one column result seperated by commas.
Example:
1,2 idx1,idx2, here,there
select stuff(T.X.query('Key1').value('.', 'varchar(max)'), 1, 1, '') as Key1,
stuff(T.X.query('Key2').value('.', 'varchar(max)'), 1, 1, '') as Key2,
stuff(T.X.query('ColumnKey').value('.', 'varchar(max)'), 1, 1, '') as ColumnKey
from
(
select ','+cast(Key1 as varchar(10)) as Key1,
','+Key2 as Key2,
','+ColumnKey as ColumnKey
from YourTable
order by Key1
for xml path(''), type
) T(X)
try this:
;WITH CTE as(
select *,1 rn from test101)
select (STUFF((select ','+cast(Key1 as varchar(10)) from CTE where CTE.rn=m.rn for xml path('')),1,1,'')) key1,
(STUFF((select ','+Key2 from CTE where CTE.rn=m.rn for xml path('')),1,1,'')) key2,
(STUFF((select ','+ColumnKey from CTE where CTE.rn=m.rn for xml path('')),1,1,'')) ColumnKey
from CTE m
group by rn
Are you aware that CodePlex has an open-source CLR implementation of user defined aggregate GROUP_CONCAT .Installation is as simple as running a SQL script on your server.
http://groupconcat.codeplex.com/
it has 4 group_concat implementation
GROUP_CONCAT --default delimiter is , (comma)
GROUP_CONCAT_D -- you can specify the delimiter
GROUP_CONCAT_DS -- you can specify the delimiter ,sort order (1 as asc order ,2 as desc order)
GROUP_CONCAT_S -- you can specify sort order
