This content has been marked as final. Show 8 replies
I hope i missed the DB version as Oracle10g. Apart from this do you have any concern for giving reply?
Can any one please help me on this ?
Take a look at unpivot keyword in http://docs.oracle.com/cd/E11882_01/server.112/e26088/statements_10002.htm#SQLRF01702
It's so high level and it needs much time to understand how it works. Could you please give some sample for my scenario
using http://docs.oracle.com/cd/E11882_01/server.112/e26088/statements_10002.htm#CHDFIIDD as a pattern maybe1 person found this helpful
select the_value,the_column,program from (select 'AA1234' level0_code, 'XX1234' level1_code, 'Zinc1543' level2_code, 'MU' program from dual ) unpivot (the_value for the_column in (level0_code as 'level0_code', level1_code as 'level1_code', level2_code as 'level2_code' ) )
Thanks Edbin, Appreciate your prompt responses.
A beautiful use of UNPIVOT Etbin, and a very nice solution. I need to remember and use PIVOT/UNPIVOT.
Unfortunately the original poster says the version is Oracle 10g, and UNPIVOT was introduced in Oracle 11.1
The (more clumsy) Oracle 10.2 version is
The (select level lvl from dual connect by level <= 3) generates numbers 1,2,3 to use to pick out the level0, level1 and level2 codes respectively.
select seq_id.nextval, q.* from (select CASE p.lvl when 1 then level0_code when 2 then level1_code when 3 then level2_code end as code , CASE p.lvl when 1 then 'Level0' when 2 then 'Level1' when 3 then 'Level2' end as lev , program from (select 'AA1234' level0_code, 'XX1234' level1_code, 'Zinc1543' level2_code, 'MU' program from dual ) src cross join (select level lvl from dual connect by level <= 3) p order by level0_code, level1_code, level2_code, program ) q
If you want the ID's from a single row sequential, you will need an "order by" - otherwise the query could come out with all rows for level0 followed by all rows for level1 etc. If you don't mind the order then you can omit the outer select and the order by.