You Asked

Hello Tom,

I am looking for a solution in oracle to create all the possible combinations from 4 different columns values.

NAME ID  CODE POS_4    POS_5 POS_6  
ABC  18  SAJ   A       null  null   
ABC  18  SAJ   null    N     null   
ABC  18  SAJ   null    A     null  
ABC  18  SAJ   null    B     null  
ABC  18  SAJ   null    F     null   
ABC  18  SAJ   null    null  H      
ABC  18  SAJ   null    null  03     
ABC  18  SAJ   null    null  null   
ABC  18  SAJ   null    null  null  

So combination I am asking for would be like-
A-N-H
A-N-03
A-A-H
A-A-03
A-B-H
A-B-03
A-F-H
A-F-03

Could you please help me with the idea or a sample code to achieve this in oracle.

and Chris said...

Here's one way to approach it:

* Cross join the table to itself once for each column you want to generate the combinations for.
* Filter out the null values for one of the columns for each copy of the table you join in.
* Concatenate the values for the column you excluded the nulls from for each table

So if you have a table like this:

with rws as (
  select case mod ( level, 2 ) 
           when 0 then 'C1' || chr ( level+65 ) 
         end c1,
         case mod ( level, 3 ) 
           when 0 then 'C2' || chr ( level+65 ) 
         end c2,
         case mod ( level, 5 ) 
           when 0 then 'C3' || chr ( level+65 ) 
         end c3
  from   dual
  connect by level <= 6
)
  select *
  from   rws;
  
C1        C2        C3       
<null>    <null>    <null>    
C1C       <null>    <null>    
<null>    C2D       <null>    
C1E       <null>    <null>    
<null>    <null>    C3F       
C1G       C2G       <null>

The query looks like this:

with rws as (
  select case mod ( level, 2 ) 
           when 0 then 'C1' || chr ( level+65 ) 
         end c1,
         case mod ( level, 3 ) 
           when 0 then 'C2' || chr ( level+65 ) 
         end c2,
         case mod ( level, 5 ) 
           when 0 then 'C3' || chr ( level+65 ) 
         end c3
  from   dual
  connect by level <= 6
)
  select r1.c1 || '-' || r2.c2 || '-' || r3.c3
  from   rws r1
  cross  join rws r2
  cross  join rws r3
  where  r1.c1 is not null
  and    r2.c2 is not null
  and    r3.c3 is not null;
  
R1.C1||'-'||R2.C2||'-'||R3.C3   
C1C-C2D-C3F                      
C1C-C2G-C3F                      
C1E-C2D-C3F                      
C1E-C2G-C3F                      
C1G-C2D-C3F                      
C1G-C2G-C3F

Rating

(2 ratings)

Is this answer out of date? If it is, please let us know via a Comment

Comments

Another solution just for fun

Sayan Malakshinov, September 06, 2021 - 4:10 pm UTC

Another solution just for fun a bit more flexible: it allows to modify column names in just one place - in the unpivot clause:

with vals as (
   select/*+ materialize */ 
      distinct 
        col,
        val, 
        dense_rank()over(order by col) dr 
   from t 
   unpivot(val for col in (POS_4, POS_5, POS_6))
 )
select
   substr(SYS_CONNECT_BY_PATH(val,'-'),2) str
from vals
where CONNECT_BY_ISLEAF =1
start with dr=1
connect by prior dr+1=dr;

"materialize" hint there is just to workaround a bug on Oracle 19.

Full example with test data:

with t(NAME, ID,  CODE, POS_4, POS_5, POS_6) as (
select 'ABC', 18, 'SAJ', 'A' ,   null, null from dual union all
select 'ABC', 18, 'SAJ', null,   'N' , null from dual union all
select 'ABC', 18, 'SAJ', null,   'A' , null from dual union all
select 'ABC', 18, 'SAJ', null,   'B' , null from dual union all
select 'ABC', 18, 'SAJ', null,   'F' , null from dual union all
select 'ABC', 18, 'SAJ', null,   'F' , null from dual union all
select 'ABC', 18, 'SAJ', null,   null, 'H'  from dual union all 
select 'ABC', 18, 'SAJ', null,   null, '03' from dual union all 
select 'ABC', 18, 'SAJ', null,   null, null from dual union all
select 'ABC', 18, 'SAJ', null,   null, null from dual
)
,vals as (
   select/*+ materialize */ 
      distinct 
        col,
        val, 
        dense_rank()over(order by col) dr 
   from t 
   unpivot(val for col in (POS_4, POS_5, POS_6)) -- specify columns here
 )
select
   substr(SYS_CONNECT_BY_PATH(val,'-'),2) str
from vals
where CONNECT_BY_ISLEAF =1
start with dr=1
connect by prior dr+1=dr;

STR
---------------
A-B-H
A-B-03
A-A-H
A-A-03
A-F-H
A-F-03
A-N-H
A-N-03

September 07, 2021 - 7:15 am UTC

Nice!

Re: Another solution just for fun

Narendra, September 06, 2021 - 8:13 pm UTC

Chris and Sayan,

Thank you once again for blowing me away by these sqls.

Sayan,

Would you know bug number in 19 that is forcing you to use materialize hint?

Thanks,

More to Explore

SQL

The Oracle documentation contains a complete SQL reference.

Breadcrumb

Question and Answer

You Asked

and Chris said...

Rating

Comments

Another solution just for fun

Re: Another solution just for fun

More to Explore

SQL

All combinations from 4 columns values in a table

Breadcrumb

Question and Answer

You Asked

and Chris said...

Rating

Comments

Another solution just for fun

Re: Another solution just for fun

More to Explore

SQL