hi everyone,
i have an scenario where i want to unpivot multiple columns. consider the following data:
with table1 as
(
select 2018 year, 'michael' name, 'male' gender, 123 cid, 'YTR' code1, 'metro' pyr1, 'metro' plan1, 'MC' group1_in1, 'MMC' group2_in1,
'EUR' code2, 'horizon' pyr2, 'horizon' plan2, 'Mo' group1_in2, 'MC' group2_in2, 'PPP' code3, 'firt' pyr3, 'first' plan3, 'MM' group1_in3, 'MC' group2_in3 from dual union all
select 2018 year, 'Will' name, 'female' gender, 678 cid, 'IUT' code1, 'metro1' pyr1, 'metro1' plan1, 'MC1' group1_in1, 'MMC1' group2_in1,
'EUR1' code2, 'mtro' pyr2, 'mtro' plan2, 'Mo' group1_in2, 'MC' group2_in2, 'PPP' code3, 'firt' pyr3, 'first' plan3, 'MM' group1_in3, 'MC' group2_in3 from dual
)
i want to unpivot and produce the following output
year name gender cid code pyr plan group1 group2 ins
2018 michael male 123 YTR metro metro MC MMC 1
2018 michael male 123 EUR horizon horizon Mo MC 2
2018 michael male 123 PPP firt firt MM MC 3
2018 Will female 678 IUT metro1 metro1 MC1 MMC1 1
2018 Will female 678 EUR1 mtro mtro Mo MC 2
2018 Will female 678 PPP firt firt MM MC 3
as you can see, instead of having code1, code2, code3 i unpivot and have one code column called code.
the same apply for other columns. the INS column is a derive column that tells you which code the data belongs to.
for example, the first row in the data set belongs to the data for code1, the second row belong to data set for code2 and so on.
i am aware that the syntax for unpivot is
SELECT ... FROM ... UNPIVOT ( <value_column> FOR <name_column> IN ( <column_list> ) )
however, i can do unpivot for one column but dont know how to unpivot for multiple columns so that i can have columns such as
code pyr plan group1 group2 ins instead of multiples code columns etc.
can somone help write an unpivot query that produce the above output? i am using oracle 11g
thanks in advance