Oracle SQL - Combine two columns into one

Question

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

So you want a [hierarchical query](http://docs.oracle.com/cd/E11882_01/server.112/e41084/queries003.htm)? How do you know to start from A? — Alex Poole, Jan 21 '16 at 16:18
There is another query which tells me the start point. Yes I want a hierarchical Query. Just like Train starts from Station A and there are many stations. So all routes a train can go to reach the end point. — badkarma, Jan 21 '16 at 16:24
Your example isnt clear. Please read http://stackoverflow.com/help/how-to-ask Here is a great place to [**START**](http://spaghettidba.com/2015/04/24/how-to-post-a-t-sql-question-on-a-public-forum/) — Juan Carlos Oropeza, Jan 21 '16 at 16:27
Why do you need the routes to be split in different rows? How are you planning to use the data? — Mehdi, Jan 21 '16 at 16:40

score 0 · Accepted Answer · answered Jan 21 '16 at 16:42

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;

score 0 · Answer 2 · answered Jan 21 '16 at 16:43

Would something like this be what you're after?

with sample_data 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 union all
                     select 'A' col1, 'F' col2 from dual)
select connect_by_root(col1)||sys_connect_by_path(col2, '-') route
from   sample_data
where  connect_by_isleaf = 1
connect by prior col2 = col1
start with col1 = 'A';

ROUTE
---------
A-B-C-D
A-B-C-E
A-F

Oracle SQL - Combine two columns into one

2 Answers2