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