SQL Server : group by one column and get all distinct elements-CodePudding

I have a demo table like:

colA  colB  
-----------  
a     apple  
a     grape  
a     apple  
a     apple  
b     lion  
b     tiger

How to get the distinct values for each element of colA?

Required output (two tables):

a apple  
a grape  
b lion  
b tiger

and

a [apple,grape]  
b [lion,tiger]

CodePudding user response：

Output 1 is simple:

SELECT colA, colB
FROM mytable
GROUP BY colA, colB
ORDER BY colA, colB

But if you really do want two "tables" for the output, you must run two queries:

SELECT colA, colB
FROM mytable
WHERE colA = 'a'
GROUP BY colA, colB

and

SELECT colA, colB
FROM mytable
WHERE colA = 'b'
GROUP BY colA, colB

How do you know to use a and b? You must first run a third query to get those values, and then loop through the results to run the first query for each result you find.

It should be obvious the better option is just running the basic query at the top of the answer, and separating the data in the client code or reporting tool.

Output 2 is typically not done in the database itself at all. This kind of pivot is also usually better handled in the client code or reporting tool. But, depending on what version of SQL Server you have, it is possible like this:

SELECT colA, string_agg(colB, ',')
FROM mytable
GROUP BY colA