DT | A | B | C | D | E | F |
1101 | 1 | 2 | 3 | 4 | 5 | 6 |
1102 | 2 | 3 | 4 | 5 | 6 | 1 |
1103 | 3 | 4 | 5 | 6 | 1 | 2 |
1104 | 4 | 5 | 6 | 1 | 2 | 3 |
1105 | 5 | 6 | 1 | 2 | 3 | 4 |
Character | 1101 | 1102 | 1103 | 1104 | 1105 |
A | 1 | 2 | 3 | 4 | 5 |
B | 2 | 3 | 4 | 5 | 6 |
C | 3 | 4 | 5 | 6 | 1 |
D | 4 | 5 | 6 | 1 | 2 |
E | 5 | 6 | 1 | 2 | 3 |
F | 6 | 1 | 2 | 3 | 4 |
with t(dt, a, b, c, d, e, f) as ( select 1101 ,1 ,2 ,3 ,4 ,5 ,6 from dual union all select 1102, 2, 3, 4, 5, 6, 1 from dual union all select 1103, 3, 4, 5, 6, 1, 2 from dual union all select 1104, 4, 5, 6, 1, 2, 3 from dual union all select 1105, 5, 6, 1, 2, 3, 4 from dual ) select Character , max(case when dt = 1101 then value end) "1101" , max(case when dt = 1102 then value end) "1102" , max(case when dt = 1103 then value end) "1103" , max(case when dt = 1104 then value end) "1104" , max(case when dt = 1105 then value end) "1105" from ( select dt , decode(no, 1, 'A', 2, 'B', 3, 'C', 4, 'D', 5, 'E', 6, 'F') Character , decode(no, 1, a, 2, b, 3, c, 4, d, 5, e, 6, f) value from t , (select level no from dual connect by level <= 10) where no <= 6 order by 1,2 ) group by Character order by Character; with t(dt, a, b, c, d, e, f) as ( select 1101 ,1 ,2 ,3 ,4 ,5 ,6 from dual union all select 1102, 2, 3, 4, 5, 6, 1 from dual union all select 1103, 3, 4, 5, 6, 1, 2 from dual union all select 1104, 4, 5, 6, 1, 2, 3 from dual union all select 1105, 5, 6, 1, 2, 3, 4 from dual ) select * from ( select * from t unpivot include nulls (val for Character in (a,b,c,d,e,f)) ) pivot (max(val) for dt in (1101,1102,1103,1104,1105)) order by Character;