Skip to content

Grouping the common values in Oracle

I have a table with sample values as below

enter image description here

In this table, all the values in Col1 will have its supporting values in Col2. The values A1 and A2 are like master values and they will never appear in Col2. I need to make an output displaying this master values in a new column like below

enter image description here

What would be the best way to achieve this in Oracle SQL?



Looks like a hierarchical query:

SQL> select connect_by_root t.col1 as main,
  2         t.col1,
  3         t.col2
  4  from test t
  5  start with t.col1 in ('A1', 'A2')
  6  connect by t.col1 = prior t.col2
  7  order by main, t.col1, t.col2;

----- ----- -----
A1    A1    B1
A1    A1    B2
A1    A1    B3
A1    B1    C1
A1    B2    C2
A1    C1    D1
A2    A2    E1
A2    A2    E2
A2    E1    F1
A2    E1    F2

10 rows selected.

3 People found this is helpful