sql - 逗号分隔结果多列sql server

Question

我有一个包含值的表:

Key1     Key2     ColumnKey 
============================
1        idx1      here
2        idx2      there

我需要返回，因为不止一列结果用逗号分隔。

示例:

1,2   idx1,idx2,      here,there

Answer 1

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