I need your help how to summarize from grouping dataset.
docno | acc |
---|---|
doc1 | A |
doc1 | B |
doc2 | B |
doc2 | C |
doc3 | A |
doc3 | B |
doc4 | A |
doc4 | B |
The output number 1, I want to expect is:
acc_combined | docno |
---|---|
A;B | doc1 |
B;C | doc2 |
A;B | doc3 |
A;B | doc4 |
The output number 2:
acc_combined | count |
---|---|
A;B | 3 |
B;C | 1 |
Column acc_combined can be dynamic value such as D;E, A;B;C, C;D;E, A;E, etc.
CodePudding user response:
To get the output you're looking for, where StackOverflow is a table formatted according to the first table you provided (accept I accidentally misspelled acc as accn, my bad):
WITH First_CTE AS (
SELECT docno,
STRING_AGG(accn, ';') AS acc_combined
FROM StackOverflow
GROUP BY docno
) SELECT acc_combined,
COUNT(docno) AS count
FROM First_CTE
GROUP BY acc_combined