使用Group By时，使用SQL -逗号分隔数据

I'm using SQL Server and TSQL:

我使用的是SQL Server和TSQL:

What I would like to do is comma separate values on one column when using a group by on another column. See data example below.

我想做的是，当在另一列上使用group by时，在一列上用逗号分隔值。请参阅下面的示例数据。

col1 --- col2

1121     abc
1123     aee
1335     afg
1121     def
1121     abc

I would like to Group By on "col1" and count the number of records, but I would also like to concatenate on col2 if the data is different. For instance, using value "1121" as a reference, see the data output below.

我想在“col1”上分组并计算记录的数量，但如果数据不同，我也想在col2上进行连接。例如，使用值“1121”作为引用，请参阅下面的数据输出。

qty --- col1 --- col2

3       1121     abc, def
1       1123     aee
1       1335     afg

I thought of maybe using COALESCE, but I'm not sure how to implement it when using group by on another column.

我想过可能使用COALESCE，但我不确定在另一篇专栏文章中使用group by时如何实现它。

Any help would be greatly appreciated.

如有任何帮助，我们将不胜感激。

1 个解决方案

#1

Here's a complete, tested, working example.

这里有一个完整的、经过测试的、工作的示例。

create table tmp (col1 varchar(100), col2 varchar(100));
insert into tmp values ('1121',    'abc');
insert into tmp values ('1123',    'aee');
insert into tmp values ('1335',    'afg');
insert into tmp values ('1121',    'def');
insert into tmp values ('1121',    'abc');

SELECT 
distinct r.col1,
       STUFF((SELECT distinct ','+ a.col2
               FROM tmp a
             WHERE r.col1 = a.col1
            FOR XML PATH(''), TYPE).value('.','VARCHAR(max)'), 1, 1, ''),
       (select COUNT(*) cnt from tmp a where r.col1 = a.col1) cnt
 FROM tmp r

Result

结果

1121    abc,def 3
1123    aee     1
1335    afg     1

References: Used OMG Ponies' answer here as a guide.

参考资料:使用OMG小马的回答作为指南。

#1