select max(decode(col1,1,col2)) as "1", max(decode(col1,2,col2)) as "2", max(decode(col1,3,col2)) as "3", max(decode(col1,4,col2)) as "4", max(decode(col1,5,col2)) as "5" from a
Connected to Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 Connected as bm
SQL> SQL> WITH A AS (SELECT 1 CD1,0 CD2 FROM DUAL 2 UNION 3 SELECT 2 CD1,0 CD2 FROM DUAL 4 UNION 5 SELECT 3 CD1,0 CD2 FROM DUAL 6 UNION 7 SELECT 4 CD1,0 CD2 FROM DUAL 8 UNION 9 SELECT 5 CD1,0 CD2 FROM DUAL 10 ) 11 SELECT * FROM (SELECT MAX(DECODE(RN,1,CD1,NULL)) ID1, 12 MAX(DECODE(RN,2,CD1,NULL)) ID2, 13 MAX(DECODE(RN,3,CD1,NULL)) ID3, 14 MAX(DECODE(RN,4,CD1,NULL)) ID4, 15 MAX(DECODE(RN,5,CD1,NULL)) ID5 16 FROM (SELECT CD1,CD2,ROW_NUMBER()OVER( ORDER BY CD1) RN FROM A) 17 UNION 18 SELECT MAX(DECODE(RN,1,CD2,NULL)) ID1, 19 MAX(DECODE(RN,2,CD2,NULL)) ID2, 20 MAX(DECODE(RN,3,CD2,NULL)) ID3, 21 MAX(DECODE(RN,4,CD2,NULL)) ID4, 22 MAX(DECODE(RN,5,CD2,NULL)) ID5 23 FROM (SELECT CD1,CD2,ROW_NUMBER()OVER( ORDER BY CD1) RN FROM A)) 24 ORDER BY ID1 DESC 25 ;
SQL> Connected to Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 Connected as bm
SQL> SQL> WITH A AS (SELECT 1 CD1,0 CD2 FROM DUAL 2 UNION 3 SELECT 2 CD1,0 CD2 FROM DUAL 4 UNION 5 SELECT 3 CD1,0 CD2 FROM DUAL 6 UNION 7 SELECT 4 CD1,0 CD2 FROM DUAL 8 UNION 9 SELECT 5 CD1,0 CD2 FROM DUAL 10 ) 11 SELECT * FROM (SELECT MAX(DECODE(RN,1,CD1,NULL)) ID1, 12 MAX(DECODE(RN,2,CD1,NULL)) ID2, 13 MAX(DECODE(RN,3,CD1,NULL)) ID3, 14 MAX(DECODE(RN,4,CD1,NULL)) ID4, 15 MAX(DECODE(RN,5,CD1,NULL)) ID5 16 FROM (SELECT CD1,CD2,ROW_NUMBER()OVER( ORDER BY CD1) RN FROM A) 17 UNION 18 SELECT MAX(DECODE(RN,1,CD2,NULL)) ID1, 19 MAX(DECODE(RN,2,CD2,NULL)) ID2, 20 MAX(DECODE(RN,3,CD2,NULL)) ID3, 21 MAX(DECODE(RN,4,CD2,NULL)) ID4, 22 MAX(DECODE(RN,5,CD2,NULL)) ID5 23 FROM (SELECT CD1,CD2,ROW_NUMBER()OVER( ORDER BY CD1) RN FROM A)) 24 ORDER BY ID1 DESC 25 ;
select max(decode(col1,1,col2)) as "1",
max(decode(col1,2,col2)) as "2",
max(decode(col1,3,col2)) as "3",
max(decode(col1,4,col2)) as "4",
max(decode(col1,5,col2)) as "5"
from a
Connected as bm
SQL>
SQL> WITH A AS (SELECT 1 CD1,0 CD2 FROM DUAL
2 UNION
3 SELECT 2 CD1,0 CD2 FROM DUAL
4 UNION
5 SELECT 3 CD1,0 CD2 FROM DUAL
6 UNION
7 SELECT 4 CD1,0 CD2 FROM DUAL
8 UNION
9 SELECT 5 CD1,0 CD2 FROM DUAL
10 )
11 SELECT * FROM (SELECT MAX(DECODE(RN,1,CD1,NULL)) ID1,
12 MAX(DECODE(RN,2,CD1,NULL)) ID2,
13 MAX(DECODE(RN,3,CD1,NULL)) ID3,
14 MAX(DECODE(RN,4,CD1,NULL)) ID4,
15 MAX(DECODE(RN,5,CD1,NULL)) ID5
16 FROM (SELECT CD1,CD2,ROW_NUMBER()OVER( ORDER BY CD1) RN FROM A)
17 UNION
18 SELECT MAX(DECODE(RN,1,CD2,NULL)) ID1,
19 MAX(DECODE(RN,2,CD2,NULL)) ID2,
20 MAX(DECODE(RN,3,CD2,NULL)) ID3,
21 MAX(DECODE(RN,4,CD2,NULL)) ID4,
22 MAX(DECODE(RN,5,CD2,NULL)) ID5
23 FROM (SELECT CD1,CD2,ROW_NUMBER()OVER( ORDER BY CD1) RN FROM A))
24 ORDER BY ID1 DESC
25 ;
ID1 ID2 ID3 ID4 ID5
---------- ---------- ---------- ---------- ----------
1 2 3 4 5
0 0 0 0 0
SQL> Connected to Oracle Database 10g Enterprise Edition Release 10.2.0.1.0
Connected as bm
SQL>
SQL> WITH A AS (SELECT 1 CD1,0 CD2 FROM DUAL
2 UNION
3 SELECT 2 CD1,0 CD2 FROM DUAL
4 UNION
5 SELECT 3 CD1,0 CD2 FROM DUAL
6 UNION
7 SELECT 4 CD1,0 CD2 FROM DUAL
8 UNION
9 SELECT 5 CD1,0 CD2 FROM DUAL
10 )
11 SELECT * FROM (SELECT MAX(DECODE(RN,1,CD1,NULL)) ID1,
12 MAX(DECODE(RN,2,CD1,NULL)) ID2,
13 MAX(DECODE(RN,3,CD1,NULL)) ID3,
14 MAX(DECODE(RN,4,CD1,NULL)) ID4,
15 MAX(DECODE(RN,5,CD1,NULL)) ID5
16 FROM (SELECT CD1,CD2,ROW_NUMBER()OVER( ORDER BY CD1) RN FROM A)
17 UNION
18 SELECT MAX(DECODE(RN,1,CD2,NULL)) ID1,
19 MAX(DECODE(RN,2,CD2,NULL)) ID2,
20 MAX(DECODE(RN,3,CD2,NULL)) ID3,
21 MAX(DECODE(RN,4,CD2,NULL)) ID4,
22 MAX(DECODE(RN,5,CD2,NULL)) ID5
23 FROM (SELECT CD1,CD2,ROW_NUMBER()OVER( ORDER BY CD1) RN FROM A))
24 ORDER BY ID1 DESC
25 ;
ID1 ID2 ID3 ID4 ID5
---------- ---------- ---------- ---------- ----------
1 2 3 4 5
0 0 0 0 0
SQL>