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