CODEWITHSUNDEEP
sqloracle-database
coderatchet
coderatchet

Reputation: 8428

Superset of column value in table SQL

How do i create a superset of value combinations from a (small, about 1-7 rows) SQL table?

e.g. i have a table:

MY_PATTERNS
╔═════════╗
║ PATTERN ║
╠═════════╣
║      30 ║
║      60 ║
║      90 ║
╚═════════╝

And am interested in finding it's super set (non-order-sensitive):

LISTAGG_PERMUTATIONS
╔══════════════════════╗
║ PATTERN_COMBINATIONS ║
╠══════════════════════╣
║ 30                   ║
║ 30,60                ║
║ 30,60,90             ║
║ 30,90                ║
║ 60,                  ║
║ 60,90                ║
║ 90                   ║
╚══════════════════════╝

Is there an SQL query to acheive this result? All my current efforts have been futile so far...

I am running Oracle 12c

I don't plan on running this on a set greater than 6 or 7 so O(n!) performance is not a concern.

Upvotes: 2

Views: 468

Answers (2)

Popeye
Popeye

Reputation: 35910

You can simply use SYS_CONNECT_BY_PATH as following

SQL> WITH MY_PATTERNS (PATTERN_ )
  2  AS
  3  (
  4  SELECT 30 FROM DUAL UNION ALL
  5  SELECT 60 FROM DUAL UNION ALL
  6  SELECT 90 FROM DUAL
  7  ) -- Your query starts from here
  8  SELECT LTRIM(SYS_CONNECT_BY_PATH(PATTERN_, ','), ',')  AS combination
  9  FROM MY_PATTERNS
 10  CONNECT BY PRIOR PATTERN_ < PATTERN_
 11  ;

COMBINATION
--------------------------------------------------------------------------------
30
30,60
30,60,90
30,90
60
60,90
90

7 rows selected.

SQL>

Upvotes: 2

Gordon Linoff
Gordon Linoff

Reputation: 1270081

You can use a recursive CTE for this:

with cte(pattern_combination, l) as (
      select pattern as pattern_combination, pattern as l
      from my_patterns
      union all
      select cte.pattern_combination || ',' || p.pattern, p.pattern
      from cte join
           my_patterns p
           on p.pattern > cte.l
     )
select pattern_combination
from cte

Here is a db<>fiddle.

Upvotes: 3

Related Questions