Home > Enterprise >  SQL query grouping dynamic value
SQL query grouping dynamic value

Time:01-27

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 
  •  Tags:  
  • sql
  • Related