SQL - Comma Separate Data When Using Group By

Question

I'm using SQL Server and 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.

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.

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.

Any help would be greatly appreciated.

see http://stackoverflow.com/questions/149772/how-to-use-group-by-to-concatenate-strings-in-mysql — stb, Jul 05 '12 at 14:08
@stb Yes, I should have been more specific. I'm using SQL Server and TSQL. — Will, Jul 05 '12 at 14:12
Maybe something like this http://stackoverflow.com/a/451441/1424450 — benRollag, Jul 05 '12 at 14:19
SQL is not for formatting data. After all, what would you do with the comma-separated list except display it, so why not construct it at the application level where it is only needed anyway? Just `SELECT ... ORDER BY col1`, and calculate `COUNT` and construct the list while fetching the rows. Start a new count and list whenever you encounter a different `col1` (thanks to `ORDER BY`). — Branko Dimitrijevic, Jul 05 '12 at 14:33
@benRollag: Tested and confirmed the solution in that post works. — Peter Majeed, Jul 05 '12 at 14:35
@benRollag: No, but since it included a `GROUP BY` clause, adding a `COUNT` on `col1` was trivial. — Peter Majeed, Jul 05 '12 at 14:50
possible duplicate of [Simulating group_concat MySQL function in MS SQL Server 2005?](http://stackoverflow.com/questions/451415/simulating-group-concat-mysql-function-in-ms-sql-server-2005) — Jerry Coffin, Jul 10 '12 at 05:59

score 5 · Accepted Answer · edited May 23 '17 at 11:56

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.

SQL - Comma Separate Data When Using Group By

1 Answers1