Sets From a Single Table, Grouped By a Column

Question

I have a table:

+-------+-------+----------+
| GROUP | State | Priority |
+-------+-------+----------+
|   1   |  MI   |     1    |
|   1   |  IA   |     2    |
|   1   |  CA   |     3    |
|   1   |  ND   |     4    |
|   1   |  AZ   |     5    |
|   2   |  IA   |     2    |
|   2   |  NJ   |     1    |
|   2   |  NH   |     3    |

And so on...

How do I write a query that makes all the sets of the states by group, in priority order? Like so:

+-------+--------------------+
| GROUP |        SET         |
+-------+--------------------+
|   1   | MI                 |
|   1   | MI, IA             |
|   1   | MI, IA, CA         |
|   1   | MI, IA, CA, ND     |
|   1   | MI, IA, CA, ND, AZ |
|   2   | NJ                 |
|   2   | NJ, IA             |
|   2   | NJ, IA, NH         |
+-------+--------------------+

This is similar to my question here and I've tried to modify that solution but, I'm just a forty watt bulb and it's a sixty watt problem...

score 2 · Accepted Answer · answered Apr 05 '19 at 15:35

This problem actually looks simpler than the answer to the question you linked, which is an excellent solution to that problem. Nevertheless, this uses the same hierarchical queries, with connect by

If it is the case that priority is always a continuous sequence of numbers, this will work

SELECT t.grp, level, ltrim(SYS_CONNECT_BY_PATH(state,','),',')   as "set"  
   from  t 
   start with priority = 1
 connect by   priority = prior priority + 1
            and grp = prior grp

However, if that's not always true, we would require row_number() to define the sequence based on the order of priority ( which need not be consecutive integer)

with t2 AS
( 
  select t.*, row_number() 
        over ( partition by grp order by priority) as rn from t
)
SELECT t2.grp, ltrim(SYS_CONNECT_BY_PATH(state,','),',')   as "set"
   from  t2 
   start with priority = 1
 connect by   rn = prior rn + 1
            and grp = prior grp

DEMO

Terrific! This is a great, elegant answer! Thanks so much, Kaushik! — anonimitie, Apr 05 '19 at 15:53

Brian Leach · Answer 2 · 2019-04-05T22:14:49.270

I realize this has already been answered, but I wanted to see if I could do this using ANSI standard syntax. "connect by" is an Oracle only feature, the following will work on multiple databases:

WITH
    -- ASET is just setting up the sample dataset
    aset AS
        (SELECT 1 AS grp, 'MI' AS state, 1 AS priority FROM DUAL
         UNION ALL
         SELECT 1 AS grp, 'IA', 2 FROM DUAL
         UNION ALL
         SELECT 1 AS grp, 'CA', 3 FROM DUAL
         UNION ALL
         SELECT 1 AS grp, 'ND', 4 FROM DUAL
         UNION ALL
         SELECT 1 AS grp, 'AZ', 5 FROM DUAL
         UNION ALL
         SELECT 2 AS grp, 'IA', 2 FROM DUAL
         UNION ALL
         SELECT 2 AS grp, 'NJ', 1 FROM DUAL
         UNION ALL
         SELECT 2 AS grp, 'NH', 3 FROM DUAL),
    bset AS
        -- In BSET we convert the ASET records into comma separated values
        (  SELECT grp, LISTAGG( state, ',' ) WITHIN GROUP (ORDER BY priority) AS set1
             FROM aset
         GROUP BY grp),
    cset ( grp
         , set1
         , set2
         , pos ) AS
        -- CSET breaks our comma separated values up into multiple rows
        -- Each row adding the next CSV value
        (SELECT grp                                                         AS grp
              , set1                                                        AS set1
              , SUBSTR( set1 || ',', 1, INSTR( set1 || ',', ',' ) - 1 )     AS set2
              , 1                                                           AS pos
           FROM bset
         UNION ALL
         SELECT grp              AS grp
              , set1             AS set1
              , SUBSTR( set1 || ','
                      , 1
                      ,   INSTR( set1 || ','
                               , ','
                               , 1
                               , pos + 1 )
                        - 1 )    AS set2
              , pos + 1          AS pos
           FROM cset
          WHERE INSTR( set1 || ','
                     , ','
                     , 1
                     , pos + 1 ) > 0)
  SELECT grp, set2
    FROM cset
ORDER BY grp, pos;

Sets From a Single Table, Grouped By a Column

2 Answers2

Linked