comma separated result multiple columns sql server

Question

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

Same as Same as http://stackoverflow.com/questions/941103/concat-groups-in-sql-server I think — Barmar, Sep 01 '12 at 06:54

score 5 · Accepted Answer · edited Jan 18 '21 at 12:30

5

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)

SE-Data

edited Jan 18 '21 at 12:30

Community

1
1

answered Sep 01 '12 at 08:00

Mikael Eriksson

132,594
21
199
273

Can someone share the name of the technique used here? Specifically the T(X) => T.X.query('Key1') – Sal Jul 13 '16 at 14:33

score 0 · Answer 2 · answered Sep 01 '12 at 09:01

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

score 0 · Answer 3 · answered Sep 02 '12 at 03:51

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

comma separated result multiple columns sql server

3 Answers3