Return all possible values of a columns while also grouping by values in another column in SQL

Question

how to group by the value in one column and append all the possible values in another column,

e.g.

   Col1, Col2
    ----  ----
    1     a
    2     a
    3     a
    1     b
    2     b
    4     c

and how to get below:

 Col1,      Col2,   count
    ----    ----   -----
    1,2,3     a     3
    1,2       b     2
    4         c     1

Most databases have some form of string aggregation - if you do as the `sql` tag suggests and TAG yours, we'll know what to recommend. — Stu, May 23 '22 at 08:59
You may be able to use STRING_AGG function if your version of SQL Server supports it — shree.pat18, May 23 '22 at 09:00

score 2 · Accepted Answer · answered May 23 '22 at 09:02

2

Assuming a modern version of SQL Server, the following would be all you need:

select String_Agg(col1,',') Col1, col2, Count(*) [count]
from t
group by col2;

answered May 23 '22 at 09:02

Stu

1 Answers1