Combinations from a Single Table, Grouped By a Column Value

Question

I have a table of values:

+-----+-------+
| ID  | STATE |
+-----+-------+
|  1  |   AL  |
|  1  |   AZ  |
|  1  |   MI  |
|  2  |   TX  |
|  2  |   TN  |
|  2  |   MO  |
|  2  |   ND  |
And many, many more...

How do I write a query that generates all of the possible state combinations for each individual ID? So the results are like:

+-----+-------------+
| ID  | COMBINATION |
+-----+-------------+
|  1  | AL, AZ      |
|  1  | AL, MI      |
|  1  | AZ, MI      |
|  1  | AL, AZ, MI  |
|  2  | TX, TN      |
|  2  | TX, MO      |
|  2  | TX, ND      |
|  2  | TN, MO      |
|  2  | TN, ND      |
  And so on...

Thanks for the help!

EDIT: Ravshan got me thinking that I do not want permutations but, bonus if the combination string is in alphabetical order.

"AL, AZ, MI" and "MI, AL, AZ" - is the same combination or not? — Ravshan Abdulaev, Mar 28 '19 at 13:03
Those would be considered the same and the query should return only one. Combinations only. *NOT* Permutations. — anonimitie, Mar 28 '19 at 13:05

score 2 · Accepted Answer · answered Mar 28 '19 at 13:17

You can do this using hierarchical queries - here's one using the old-style connect by:

WITH your_table AS (SELECT 1 ID, 'AL' state FROM dual UNION ALL
                    SELECT 1 ID, 'AZ' state FROM dual UNION ALL
                    SELECT 1 ID, 'MI' state FROM dual UNION ALL
                    SELECT 2 ID, 'TX' state FROM dual UNION ALL
                    SELECT 2 ID, 'TN' state FROM dual UNION ALL
                    SELECT 2 ID, 'MO' state FROM dual UNION ALL
                    SELECT 2 ID, 'ND' state FROM dual UNION ALL
                    SELECT 3 ID, 'OH' state FROM dual)
SELECT ID,
       state,
       ltrim(SYS_CONNECT_BY_PATH(state, ','), ',') combinations,
       LEVEL
FROM   (SELECT id,
               state,
               count(*) OVER (PARTITION BY id) state_cnt
        FROM your_table)
WHERE  state_cnt = 1
OR     (state_cnt > 1 AND LEVEL > 1)
CONNECT BY PRIOR ID = ID
           AND PRIOR state < state
           AND PRIOR sys_guid() IS NOT NULL;

        ID STATE COMBINATIONS      LEVEL
---------- ----- ------------ ----------
         1 AZ    AL,AZ                 2
         1 MI    AL,AZ,MI              3
         1 MI    AL,MI                 2
         1 MI    AZ,MI                 2
         2 TX    TN,TX                 2
         2 TX    MO,TX                 2
         2 TN    MO,TN                 2
         2 TX    MO,TN,TX              3
         2 ND    MO,ND                 2
         2 TX    MO,ND,TX              3
         2 TN    MO,ND,TN              3
         2 TX    MO,ND,TN,TX           4
         2 TX    ND,TX                 2
         2 TN    ND,TN                 2
         2 TX    ND,TN,TX              3
         3 OH    OH                    1

The prior sys_guid() is not null condition in the connect by clause is required to ensure we're looping over the correct rows (if you were to omit it, the result would contain many extra rows).

I've excluded rows with just a single state in the output - except if the id only listed a single state. You may or may not want to include single states in the output, in which case, you can remove the predicates entirely.

Combinations from a Single Table, Grouped By a Column Value

1 Answers1

Linked