Skip to content
Advertisement

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?

Advertisement

Answer

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;

MAIN  COL1  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.

SQL>
3 People found this is helpful
Advertisement