Converting row workflow data to column workflow in all possible combination in oracle

Question

I have a workflow process in my system having data in following format.

From    To
1       2
1       3
2       4
3       5
4       5
5       6
6       7
6       8
7       9
8       9

I need to convert this to following format.

S1  S2  S3  S4  S5  S6  S7
1   2   4   5   6   7   9
1   3   5   6   7   9
1   3   5   6   8   9
1   2   4   5   6   8   9

or

S1  S2  S3  S4  S5  S6  S7
1   2   4   5   6   7   9
1   3   5   6   7   9
            6   8   9   
                6   8   9

or any other useful format

The output represent the number of probable paths that can be followed during a workflow process. You can assume I have information which is my first stage and last stage. In this case you can assume 1 = first stage and 9 = final. So once the user is at Stage 1 he can choose whether to go in Stage 2 or Stage 3

Answer 1

You need hierarchical query, something like this:

select path 
  from (select stage_to end,
               sys_connect_by_path(stage_from, ' => ') || ' => ' || stage_to path
          from test
          start with stage_from = 1
          connect by stage_from = prior stage_to)
  where end = 9;

If output in one column is acceptable use sys_connect_by_path, but if you need unknown number of output columns you can try any answer from this question: Dynamic pivot in oracle sql.

Test data and output:

with test (stage_from, stage_to) as (
    select 1, 2 from dual union all
    select 1, 3 from dual union all
    select 2, 4 from dual union all
    select 3, 5 from dual union all
    select 4, 5 from dual union all
    select 5, 6 from dual union all
    select 6, 7 from dual union all
    select 6, 8 from dual union all
    select 7, 9 from dual union all
    select 8, 9 from dual)
select path 
  from (select stage_to end,
               sys_connect_by_path(stage_from, ' => ') || ' => ' || stage_to path
          from test
          start with stage_from = 1
          connect by stage_from = prior stage_to)
  where end = 9;

Output:

PATH
---------------------------------------
=> 1 => 2 => 4 => 5 => 6 => 7 => 9
=> 1 => 2 => 4 => 5 => 6 => 8 => 9
=> 1 => 3 => 5 => 6 => 7 => 9
=> 1 => 3 => 5 => 6 => 8 => 9

Answer 2

OK so the main issue i had with this is that '1' only appears in the 'From' column, while '9' only appears in the 'To' column. To counter this, I have added an extra row in the data of as per this setup. Obviously, this may not work in the real world.

create table temp1 as
select 1 as frm, 2 as too from dual
union all
select 1, 3 from dual
union all
select 2,4 from dual
union all
select 3,5 from dual
union all
select 4,5 from dual
union all
select 5,6 from dual
union all
select 6,7 from dual
union all
select 6,8 from dual
union all
select 7,9 from dual
union all
select 8,9 from dual
union all
select 9, 0 from dual

Then there is this (quite messy) code.

select
    t1a.frm as s1,
    t1b.frm as s2,
    t1c.frm as s3,
    t1d.frm as s4,
    t1e.frm as s5,
    t1f.frm as s6,
    t1g.frm as s7,
    t1h.frm as s8
from
    temp1 t1a
left join
    temp1 t1b
on T1A.too = t1b.frm
left join
    temp1 t1c
on t1b.too = t1c.frm
left join
    temp1 t1d
on t1c.too = t1d.frm
left join
    temp1 t1e
on t1d.too = t1e.frm
left join
    temp1 t1f
on t1e.too = t1f.frm
left join
    temp1 t1g
on t1f.too = t1g.frm
left join
    temp1 t1h
on t1g.too = t1h.frm
where t1a.frm = 1

Which gives the output

s1  s2  s3  s4  s5  s6  s7  s8
1   3   5   6   8   9       
1   3   5   6   7   9       
1   2   4   5   6   7   9   
1   2   4   5   6   8   9

Not sure if this is exactly what you are looking for, but hopefully it gives you some ideas