Oracle 行转列，列转行

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