# 又是個行列轉換的問題

1 2 3 4
1 2 5 6
1 2 7 8

1 2 3 4 5 6 7 8

SQL> with a as (select 1 id1,2 id2,3 id3,4 id4 from dual
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

SQL> with a as (select 1 id1,2 id2,3 id3,4 id4 from dual
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

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

• 博文量
135
• 访问量
294504