逗号分隔列表作为单个字符串，T-SQL [英] comma delimited list as a single string, T-SQL

问题描述

我的 T-SQL 查询生成以下结果集

My T-SQL query generates following result set

ID        Date
756 2011-08-29
756 2011-08-31
756 2011-09-01
756 2011-09-02

我怎样才能像这样转换

ID                Date
756 2011-08-29, 2011-08-31, 2011-09-01, 2011-09-02

任何建议将不胜感激.

推荐答案

您可以使用 FOR XML PATH 和 STUFF 将多行连接成一行:

You could use FOR XML PATH and STUFF to concatenate the multiple rows into a single row:

select distinct t1.id,
  STUFF(
         (SELECT ', ' + convert(varchar(10), t2.date, 120)
          FROM yourtable t2
          where t1.id = t2.id
          FOR XML PATH (''))
          , 1, 1, '')  AS date
from yourtable t1;

参见SQL Fiddle with Demo

这篇关于逗号分隔列表作为单个字符串，T-SQL的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！