I have a table
x
table1TEST_TYPE H_LEVEL HADOOP22RETGGEDGDD, RRMMNFNEDGDD Control Directory 1 Interchange Control Header22RETGGEDGDD, RRMMNFNEDGDD Control Directory 2 Interchange Control Header22RETGGEDGDD, RRMMNFNEDGDD Control Directory 3 Interchange Control HeaderI 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.
table2TEST_TYPE H_LEVEL HADOOP22RETGGEDGDD Control Directory 1 Interchange Control HeaderRRMMNFNEDGDD Control Directory 1 Interchange Control Header22RETGGEDGDD Control Directory 2 Interchange Control HeaderRRMMNFNEDGDD Control Directory 2 Interchange Control Header22RETGGEDGDD Control Directory 3 Interchange Control HeaderRRMMNFNEDGDD Control Directory 3 Interchange Control HeaderHow 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 HeaderRRMMNFNEDGDD Control Directory 1 Interchange Control Header22RETGGEDGDD Control Directory 2 Interchange Control HeaderRRMMNFNEDGDD Control Directory 2 Interchange Control HeaderSQL>