Search code examples
sqloracle-databaseselectunpivot

Oracle SQL - Combine two columns into one

I have a table 

Column 1       Column 2
   A              B
   B              C
   C              D
   C              E

Now I want the output like below (All possible routes from A to end point like A-B-C-D, A-B-C-E)

Column 1 
   A
   B
   C
   D
   A
   B
   C
   E
Solution

  • You need to record the path through your nodes, and only return the complete paths, so the following should get you there:

    with dat as (
select 'A' col1, 'B' col2 from dual union all
select 'B' col1, 'C' col2 from dual union all
select 'C' col1, 'D' col2 from dual union all
select 'C' col1, 'E' col2 from dual )
select ltrim(the_path,'-')||'-'||col2
from (
    select SYS_CONNECT_BY_PATH(col1, '-') the_path
          ,CONNECT_BY_ISLEAF is_leaf
          ,col2
    from dat
    start with col1 = 'A'
    connect by prior col2 = col1
    ) where is_leaf = 1;