T-sql查询输出CSV格式的值

I have a values in a table as shown below. I need to get the generation times in comma seperated values for each subscriptionID.

表中有一个值，如下所示。我需要为每个subscriptionID获取逗号分隔值的生成时间。

SubscriptionID GenerationTimes

6519    NULL
6616    NULL
6617    NULL
6618    9:00:00
6618    17:00:00
6634    NULL
6698    0:00:00

I need the result as follows

我需要如下结果

SubscriptionID GenerationTimes

6519    NULL
6616    NULL
6617    NULL
6618    9:00, 17:00
6634    NULL
6698    0:00:00

you notice for subscriptionID 6618 there are two generation times and so I have to put them in one row with comma ',' separated. Please give me suggestion how i can write the T-sql statement.

注意到subscriptionID 6618有两代人，所以我必须把它们放在一行中，用逗号'，'分隔。请给我如何写T-sql语句的建议。

1 个解决方案

#1

You can use for xml path like so:

您可以对xml路径使用如下:

select 
      t1.SubscriptionID,
      (select GenerationTimes + ', '
       from tableName t2
       where t1.SubscriptionID = t2.SubscriptionID
       for xml path('')) as GenerationTimes
from tableName t1
group by t1.SubscriptionID