I have a table with sample values as below
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
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>