I have a table
table1 TEST_TYPE H_LEVEL HADOOP 22RETGGEDGDD, RRMMNFNEDGDD Control Directory 1 Interchange Control Header 22RETGGEDGDD, RRMMNFNEDGDD Control Directory 2 Interchange Control Header 22RETGGEDGDD, RRMMNFNEDGDD Control Directory 3 Interchange Control Header
I know how to separate comma separated values in columns, but I don’t know how to separate values in rows while copying all the other columns as follows.
table2 TEST_TYPE H_LEVEL HADOOP 22RETGGEDGDD Control Directory 1 Interchange Control Header RRMMNFNEDGDD Control Directory 1 Interchange Control Header 22RETGGEDGDD Control Directory 2 Interchange Control Header RRMMNFNEDGDD Control Directory 2 Interchange Control Header 22RETGGEDGDD Control Directory 3 Interchange Control Header RRMMNFNEDGDD Control Directory 3 Interchange Control Header
How can I do this in Oracle SQL?
Advertisement
Answer
Here’s one option; you need lines 6 onwards.
SQL> with table1 (test_type, h_level, hadoop) as 2 (select '22RETGGEDGDD, RRMMNFNEDGDD', 'Control Directory 1', 'Interchange Control Header' from dual 3 union all 4 select '22RETGGEDGDD, RRMMNFNEDGDD', 'Control Directory 2', 'Interchange Control Header' from dual 5 ) 6 select trim(regexp_substr(test_type, '[^,]+', 1, column_value)) test_type, 7 h_level, 8 hadoop 9 from table1 join table(cast(multiset(select level from dual 10 connect by level <= regexp_count(test_type, ',') + 1 11 ) as sys.odcinumberlist )) on 1 = 1 12 order by 2, 1 desc, 3; TEST_TYPE H_LEVEL HADOOP -------------------- ------------------- -------------------------- 22RETGGEDGDD Control Directory 1 Interchange Control Header RRMMNFNEDGDD Control Directory 1 Interchange Control Header 22RETGGEDGDD Control Directory 2 Interchange Control Header RRMMNFNEDGDD Control Directory 2 Interchange Control Header SQL>