又是個行列轉換的問題
1 2 5 6
1 2 7 8
转换为
1 2 3 4 5 6 7 8
2 union
3 select 1 id1,2 id2,5 id3,6 id4 from dual
4 union
5 select 1 id1,2 id2,7 id3,8 id4 from dual
6 )
7 select id1||','||id2||','||wmsys.wm_concat(id5) id6 from
8 (select id1,id2,id3||','||id4 id5 from a)
9 group by id1,id2
10 ;
ID6
--------------------------------------------------------------------------
1,2,3,4,5,6,7,8
2 union
3 select 1 id1,2 id2,5 id3,6 id4 from dual
4 union
5 select 1 id1,2 id2,7 id3,8 id4 from dual
6 )
7
7 select id1,id2,max(decode(rn,1,id3,null)) id3,
8 max(decode(rn,1,id4,null)) id4,
9 max(decode(rn,2,id3,null)) id5,
10 max(decode(rn,2,id4,null)) id6,
11 max(decode(rn,3,id3,null)) id7,
12 max(decode(rn,3,id4,null)) id8 from
13 (select id1,id2,id3,id4,row_number()over(partition by id1,id2 order by id1,id2,id3) rn from a)
14 group by id1,id2
15 order by id1,id2
16 /
ID1 ID2 ID3 ID4 ID5 ID6 ID7 ID8
---------- ---------- ---------- ---------- ---------- ---------- ----------
1 2 3 4 5 6 7 8
第二個行列轉換的例子
如下表：
ID 名称 类型 数量
1 0001 A 3
2 0001 A 1
3 0001 B 2
4 0002 A 4
5 0002 B 6
6 0002 B 3
查询的结果，我想要的是这种形式
名称 类型A 数量 类型B 数量
0001 A 4 B 2
0002 A 4 B 9
方法一
SQL> WITH A AS (SELECT 1 ID,'0001' NAME,'A' TYPE,3 QTY FROM DUAL
2 UNION
3 SELECT 2 ID,'0001' NAME,'A' TYPE,1 QTY FROM DUAL
4 UNION
5 SELECT 3 ID,'0001' NAME,'B' TYPE,2 QTY FROM DUAL
6 UNION
7 SELECT 4 ID,'0002' NAME,'A' TYPE,4 QTY FROM DUAL
8 UNION
9 SELECT 5 ID,'0002' NAME,'B' TYPE,6 QTY FROM DUAL
10 UNION
11 SELECT 6 ID,'0002' NAME,'B' TYPE,3 QTY FROM DUAL
12 )
13 SELECT NAME,'A',SUM(DECODE(TYPE,'A',QTY,0)) QTYA,'B',SUM(DECODE(TYPE,'B',QTY,0)) QTYB
14 FROM A
15 GROUP BY NAME
SQL> /
NAME 'A' QTYA 'B' QTYB
---- --- ---------- --- ----------
0001 A 4 B 2
0002 A 4 B 9
方法二
SQL> WITH A AS (SELECT 1 ID,'0001' NAME,'A' TYPE,3 QTY FROM DUAL
2 UNION
3 SELECT 2 ID,'0001' NAME,'A' TYPE,1 QTY FROM DUAL
4 UNION
5 SELECT 3 ID,'0001' NAME,'B' TYPE,2 QTY FROM DUAL
6 UNION
7 SELECT 4 ID,'0002' NAME,'A' TYPE,4 QTY FROM DUAL
8 UNION
9 SELECT 5 ID,'0002' NAME,'B' TYPE,6 QTY FROM DUAL
10 UNION
11 SELECT 6 ID,'0002' NAME,'B' TYPE,3 QTY FROM DUAL
12 )
13 SELECT NAME,MAX(DECODE(RN,1,TYPE,NULL)) TYPEA,
14 MAX(DECODE(RN,1,QTY,NULL)) QTYA,
15 MAX(DECODE(RN,2,TYPE,NULL)) TYPEB,
16 MAX(DECODE(RN,2,QTY,NULL)) QTYB
17 FROM (SELECT NAME,TYPE,QTY,ROW_NUMBER()OVER(PARTITION BY NAME ORDER BY NAME) RN FROM (select NAME,TYPE,SUM(QTY) QTY from A
18 GROUP BY NAME,TYPE
19 ORDER BY NAME))
20 GROUP BY NAME
21 /
NAME TYPEA QTYA TYPEB QTYB
---- ----- ---------- ----- ----------
0001 A 4 B 2
0002 A 4 B 9
来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/10768286/viewspace-83494/，如需转载，请注明出处，否则将追究法律责任。
