Oracle 行转列，列转行

psbuohxipl

Oracle Pivot/Unpivot
此特性只适用于Oracle 11g 以上版本
CREATE TABLE CUSTOMERS
(
CUST_ID NUMBER(10),
CUST_NAME VARCHAR2(20),
STATE_CODE VARCHAR2(2),
TIMES_PURCHASED NUMBER(3)
);
INSERT INTO CUSTOMERS VALUES(1,'ANKER','CT',1);
INSERT INTO CUSTOMERS VALUES(1,'ANKER','ET',2);
INSERT INTO CUSTOMERS VALUES(1,'ANKER','DT',3);
INSERT INTO CUSTOMERS VALUES(1,'ANKER','QD',4);
INSERT INTO CUSTOMERS VALUES(2,'JONH','ET',2);
INSERT INTO CUSTOMERS VALUES(2,'JONH','DT',3);
INSERT INTO CUSTOMERS VALUES(2,'JONH','QD',2);
INSERT INTO CUSTOMERS VALUES(3,'QIANG','DT',3);
INSERT INTO CUSTOMERS VALUES(3,'QIANG','QD',4);
COMMIT;
--该记录显示了客户所在的州以及该客户在商店购物的次数。当该客户从商店购买更多物品时，列 times_purchased 会进行更新。
SQL> SELECT * FROM CUSTOMERS;
CUST_ID CUST_NAME            ST TIMES_PURCHASED
---------- -------------------- -- ---------------
1 Anker                CT               1
1 Anker                ET               2
1 Anker                DT               3
1 Anker                QD               4
2 Jonh                 ET               2
2 Jonh                 DT               3
2 Jonh                 QD               2
3 Qiang                DT               3
3 Qiang                QD               4
已选择9行。

PIVOT用法
--1.查看每个州，每个客户的购买频率
SELECT * FROM CUSTOMERS
PIVOT(
SUM(TIMES_PURCHASED) FOR STATE_CODE IN ('QD' AS "New York", 'CT' AS "Connecticut", 'ET' AS "New Jersey", 'DT' AS "Missouri")
);
--从结果可以看出,pivot将针对非pivot涉及的字段进行group by，pivot中的 in (...)子句中的值将作为列，聚合的值将作为列的值
CUST_ID CUST_NAME              New York Connecticut New Jersey   Missouri
---------- -------------------- ---------- ----------- ---------- ----------
1 Anker                         4           1          2          3
2 Jonh                          2                      2          3
3 Qiang                         4                                 3
--2.了解各个州的购买频率，即，各个州有多少客户只购物一次、两次、三次等等。如果使用常规 SQL
SQL> SELECT STATE_CODE, TIMES_PURCHASED, COUNT(*)
2    FROM CUSTOMERS
3   GROUP BY STATE_CODE, TIMES_PURCHASED;
ST TIMES_PURCHASED   COUNT(*)
-- --------------- ----------
QD               4          2
QD               2          1
CT               1          1
ET               2          2
DT               3          3
--这样看起来不太直观
SELECT *
FROM
(
SELECT TIMES_PURCHASED, STATE_CODE FROM CUSTOMERS) T
PIVOT(
COUNT(STATE_CODE) FOR STATE_CODE IN ('QD', 'CT', 'ET', 'DT')
)
TIMES_PURCHASED       'QD'       'CT'       'ET'       'DT'
--------------- ---------- ---------- ---------- ----------
1          0          1          0          0
2          1          0          2          0
4          2          0          0          0
3          0          0          0          3
--但是假设您希望显示州名而非缩写
SELECT *
FROM
(
SELECT TIMES_PURCHASED as "Puchase Frequency", STATE_CODE FROM CUSTOMERS) T
PIVOT(COUNT(STATE_CODE) as CNT
FOR STATE_CODE IN ('QD' AS "New York", 'CT' AS "Connecticut", 'ET' AS "New Jersey", 'DT' AS "Missouri")
);
Puchase Frequency New York_CNT Connecticut_CNT New Jersey_CNT Missouri_CNT
----------------- ------------ --------------- -------------- ------------
1            0               1              0            0
2            1               0              2            0
4            2               0              0            0
3            0               0              0            3
-----------------------------------------------------------------------------------------------------------------------------------
Unpivot 讲解
--1.将上面的pivot后的结果进行反转回来
CREATE TABLE TAB_MATRIX1
AS
SELECT * FROM CUSTOMERS
PIVOT(
SUM(TIMES_PURCHASED) FOR STATE_CODE IN ('QD' AS "NEW YORK", 'CT' AS "CONNECTICUT", 'ET' AS "NEW JERSEY", 'DT' AS "MISSOURI")
);
--查询表
SQL> SELECT * FROM TAB_MATRIX1
2  ;
CUST_ID CUST_NAME              New York Connecticut New Jersey   Missouri
---------- -------------------- ---------- ----------- ---------- ----------
1 Anker                         4           1          2          3
2 Jonh                          2                      2          3
3 Qiang                         4                                 3
--从结果中可以看出,for .. in (..)中的列名将作为state_code列的值，字段state_counts中的列将由原来的列值来代替
SQL> SELECT * FROM TAB_MATRIX1
2  unpivot
3  (
4  state_counts
5  for state_code in ("New York","Connecticut","New Jersey","Missouri")
6  );
CUST_ID CUST_NAME            STATE_CODE  STATE_COUNTS
---------- -------------------- ----------- ------------
1 Anker                New York               4
1 Anker                Connecticut            1
1 Anker                New Jersey             2
1 Anker                Missouri               3
2 Jonh                 New York               2
2 Jonh                 New Jersey             2
2 Jonh                 Missouri               3
3 Qiang                New York               4
3 Qiang                Missouri               3
已选择9行。
--将上面第二个结果也进行反转
CREATE TABLE TAB_MATRIX2 AS
SELECT *
FROM
(
SELECT TIMES_PURCHASED as "Puchase Frequency", STATE_CODE FROM CUSTOMERS) T
PIVOT(COUNT(STATE_CODE) as CNT
FOR STATE_CODE IN ('QD' AS "New York", 'CT' AS "Connecticut", 'ET' AS "New Jersey", 'DT' AS "Missouri")
);
SQL> SELECT * FROM TAB_MATRIX2;
Puchase Frequency New York_CNT Connecticut_CNT New Jersey_CNT Missouri_CNT
----------------- ------------ --------------- -------------- ------------
1            0               1              0            0
2            1               0              2            0
4            2               0              0            0
3            0               0              0            3
SELECT  * FROM TAB_MATRIX2
UNPIVOT
(
CNT_STATE_CODE
FOR STATE_CODE IN ("New York_CNT","Connecticut_CNT","New Jersey_CNT","Missouri_CNT")
);
Puchase Frequency STATE_CODE      CNT_STATE_CODE
----------------- --------------- --------------
1 New York_CNT                 0
1 Connecticut_CNT              1
1 New Jersey_CNT               0
1 Missouri_CNT                 0
2 New York_CNT                 1
2 Connecticut_CNT              0
2 New Jersey_CNT               2
2 Missouri_CNT                 0
4 New York_CNT                 2
4 Connecticut_CNT              0
4 New Jersey_CNT               0
4 Missouri_CNT                 0
3 New York_CNT                 0
3 Connecticut_CNT              0
3 New Jersey_CNT               0
3 Missouri_CNT                 3
---------------------------------------------------------------------------------------------------------------------
Oracle 11g之前的版本如何实现行转列及列转行
CREATE TABLE T
AS
SELECT * FROM
(
SELECT OWNER,OBJECT_TYPE,COUNT(*) AS CNT FROM DBA_OBJECTS
GROUP BY OWNER,OBJECT_TYPE
) WHERE ROWNUM <= 3;
admin@ORCL> SELECT * FROM T;
OWNER                          OBJECT_TYPE                CNT
------------------------------ ------------------- ----------
BI                             SYNONYM                      8
HR                             VIEW                         1
HR                             INDEX                       19
--列转行
admin@ORCL> SELECT OWNER,
2         SUM(CASE
3               WHEN OBJECT_TYPE = 'SYNONYM' THEN
4                CNT
5               ELSE
6                0
7             END) AS TYPE_SYNONYM,
8         SUM(CASE
9               WHEN OBJECT_TYPE = 'VIEW' THEN
10                CNT
11               ELSE
12                0
13             END) AS TYPE_VIEW,
14         SUM(CASE
15               WHEN OBJECT_TYPE = 'INDEX' THEN
16                CNT
17               ELSE
18                0
19             END)  AS TYPE_INDEX
20    FROM T
21   GROUP BY OWNER;
OWNER                          TYPE_SYNONYM  TYPE_VIEW TYPE_INDEX
------------------------------ ------------ ---------- ----------
HR                                        0          1         19
BI                                        8          0          0

--列转行
DROP TABLE T PURGE;
CREATE TABLE T
(
OWNER VARCHAR2(10),
TYPE_SYNONYM INT,
TYPE_VIEW INT,
TYPE_INDEX INT
);
INSERT INTO T VALUES('HR','0','1','19');
INSERT INTO T VALUES('BI','8','0','0');
COMMIT;
--查看数据
admin@ORCL> SELECT * FROM T;
OWNER      TYPE_SYNONYM  TYPE_VIEW TYPE_INDEX
---------- ------------ ---------- ----------
HR                    0          1         19
BI                    8          0          0
--列转行
admin@ORCL> SELECT OWNER, 'SYNONYM' AS TYPE, TYPE_SYNONYM
2    FROM T
3  UNION ALL
4  SELECT OWNER, 'VIEW', TYPE_VIEW
5    FROM T
6  UNION ALL
7  SELECT OWNER, 'INDEX', TYPE_INDEX FROM T;
OWNER      TYPE    TYPE_SYNONYM
---------- ------- ------------
HR         SYNONYM            0
BI         SYNONYM            8
HR         VIEW               1
BI         VIEW               0
HR         INDEX             19
BI         INDEX              0