CODEWITHSUNDEEP
sqloracle-database
Anitha Ramaiah
Anitha Ramaiah

Reputation: 89

SQL to find combinations within a column

Having a table with two columns, PART_NUMBER and PART_TYPE. A type can have many parts. The requirement is to find the unique combinations of the part numbers using a sql procedure. We are using Oracle as our DB. Sample data is as below:

PART_NO       PART_TYPE 

Part1            10

Part2            10

Part3            10 

Part4            20

Part5            30

Part6            30

Part7            40

I want the output to be as follows.

1: Part1 Part4 Part5 Part7

2: Part1 Part4 Part6 Part7

3: Part2 Part4 Part5 Part7 

4: Part2 Part4 Part6 Part7

5: Part3 Part4 Part5 Part7

6: Part3 Part4 Part6 Part7

ie) Each part from each type forms a single combination.

Please throw some light on this either in the form of algorithm or sql procedure.

Upvotes: 3

Views: 214

Answers (4)

MT0
MT0

Reputation: 167981

Use a hierarchical query:

SQL Fiddle

Oracle 11g R2 Schema Setup:

CREATE TABLE parts AS
          SELECT 'Part1' AS PART_NO, 10 AS PART_TYPE FROM DUAL
UNION ALL SELECT 'Part2' AS PART_NO, 10 AS PART_TYPE FROM DUAL
UNION ALL SELECT 'Part3' AS PART_NO, 10 AS PART_TYPE FROM DUAL
UNION ALL SELECT 'Part4' AS PART_NO, 20 AS PART_TYPE FROM DUAL
UNION ALL SELECT 'Part5' AS PART_NO, 30 AS PART_TYPE FROM DUAL
UNION ALL SELECT 'Part6' AS PART_NO, 30 AS PART_TYPE FROM DUAL
UNION ALL SELECT 'Part7' AS PART_NO, 40 AS PART_TYPE FROM DUAL;

Query 1:

WITH combinations AS (
  SELECT SYS_CONNECT_BY_PATH( PART_NO, ' ' ) AS parts,
         CONNECT_BY_ISLEAF AS leaf
  FROM   parts
  START WITH PART_TYPE = 10
  CONNECT BY PRIOR PART_TYPE + 10 = PART_TYPE
)
SELECT ROWNUM || ':' || parts AS output
FROM   combinations
WHERE  leaf = 1

Results:

|                     OUTPUT |
|----------------------------|
| 1: Part1 Part4 Part5 Part7 |
| 2: Part1 Part4 Part6 Part7 |
| 3: Part2 Part4 Part5 Part7 |
| 4: Part2 Part4 Part6 Part7 |
| 5: Part3 Part4 Part5 Part7 |
| 6: Part3 Part4 Part6 Part7 |

Edit Rob van Wijk: Since connect_by_isleaf is evaluated after the connect by, a slightly easier query is:

SQL>  select rownum || ':' || sys_connect_by_path(part_no, ' ') as parts
  2     from parts
  3    where connect_by_isleaf = 1
  4  connect by prior part_type + 10 = part_type
  5    start with part_type = 10
  6  /

PARTS
---------------------------------------------------------------------------------------
1: Part1 Part4 Part5 Part7
2: Part1 Part4 Part6 Part7
3: Part2 Part4 Part5 Part7
4: Part2 Part4 Part6 Part7
5: Part3 Part4 Part5 Part7
6: Part3 Part4 Part6 Part7

6 rows selected.

Edit - Non-incremental PART_TYPEs

SQL Fiddle

Query 3:

WITH part_types AS (
  SELECT DISTINCT PART_TYPE
  FROM   parts
),
ordered_part_types AS (
  SELECT PART_TYPE,
         LEAD( PART_TYPE ) OVER ( ORDER BY PART_TYPE ) AS NEXT_PART_TYPE
  FROM   part_types
)
SELECT ROWNUM || ':' || SYS_CONNECT_BY_PATH( PART_NO, ' ' ) AS parts
FROM   parts p
       INNER JOIN
       ordered_part_types t
       ON ( p.PART_TYPE = t.PART_TYPE )
WHERE  CONNECT_BY_ISLEAF = 1
START WITH p.PART_TYPE = ( SELECT MIN( PART_TYPE ) FROM parts )
CONNECT BY PRIOR NEXT_PART_TYPE = p.PART_TYPE

Results:

|                      PARTS |
|----------------------------|
| 1: Part3 Part4 Part6 Part7 |
| 2: Part3 Part4 Part5 Part7 |
| 3: Part2 Part4 Part6 Part7 |
| 4: Part2 Part4 Part5 Part7 |
| 5: Part1 Part4 Part6 Part7 |
| 6: Part1 Part4 Part5 Part7 |

Upvotes: 5

Qualtagh
Qualtagh

Reputation: 721

You don't need PL/SQL for this. Just SQL.

with TAB as (
  select 'Part1' as PART_NO, 10 as PART_TYPE from dual union all
  select 'Part2', 10 from dual union all
  select 'Part3', 10 from dual union all
  select 'Part4', 20 from dual union all
  select 'Part5', 30 from dual union all
  select 'Part6', 30 from dual union all
  select 'Part7', 40 from dual
),
CONSTANTS as (
  select /*+ MATERIALIZE */
         min( PART_TYPE ) as PART_TYPE,
         count( distinct PART_TYPE ) as CNT
  from TAB
)
select rownum || ':' || ANSWER as ANSWER
from ( select sys_connect_by_path( PART_NO, ' ' ) as ANSWER,
              connect_by_isleaf as IS_LEAF,
              level as L
       from TAB
       start with PART_TYPE = ( select PART_TYPE
                                from CONSTANTS )
       connect by PART_TYPE > prior PART_TYPE )
where L = ( select CNT
            from CONSTANTS )
  and IS_LEAF = 1

Upvotes: 1

Jon Tofte-Hansen
Jon Tofte-Hansen

Reputation: 794

This will give you the desired output but with caveats:

1: you have to add a table in the FROM clause for each part_type and add a "less than" line to the where clause.

2: Beware: if the base table is big the cartesian joins might make it run forever (more or less).

with tab as (
  select 'Part1' part_no, 10 part_type from dual union all
  select 'Part2', 10 from dual union all
  select 'Part3', 10 from dual union all
  select 'Part4', 20 from dual union all
  select 'Part5', 30 from dual union all
  select 'Part6', 30 from dual union all
  select 'Part7', 40 from dual
)

select 
t1.part_no||','||t2.part_no||','||t3.part_no||','||t4.part_no output
from
tab t1, tab t2, tab t3, tab t4
where
t1.part_type < t2.part_type and
t2.part_type < t3.part_type and
t3.part_type < t4.part_type
order by 1;

Upvotes: 1

guthy
guthy

Reputation: 326

You can use Oracle's listagg function (see https://docs.oracle.com/database/121/SQLRF/functions100.htm#SQLRF30030) like this:

select part_type, listagg(part_no, ' ') within group (order by part_no)
from d
group by part_type;

Upvotes: -1

Related Questions