Oracle使用SQL实现矩阵转置

在使用数据库使用报表时，往往会遇到矩阵转置。这个需求在Excel是很容易实现的，但很多人都不知道怎么用Oracle数据库实现，下面给大家展示几种使用SQL实现的方法。

需求：表1转置成表2

测试数据:

CREATE TABLE TMP AS 
SELECT 'A1' AS A, 'B1' AS B
FROM   DUAL
UNION ALL
SELECT 'A2' AS A, 'B2' AS B
FROM   DUAL
UNION ALL
SELECT 'A3' AS A, 'B3' AS B
FROM   DUAL
UNION ALL
SELECT 'A4' AS A, 'B4' AS B
FROM   DUAL
UNION ALL
SELECT 'A5' AS A, 'B5' AS B
FROM   DUAL

 

方法一：UNPIVOT+PIVOT  数据库11g以上

SELECT *
FROM   (SELECT R, COL, V
         FROM   (SELECT ROW_NUMBER() OVER(ORDER BY A, B) AS R, A, B
                  FROM   TMP) UNPIVOT(V FOR COL IN(A, B)))
PIVOT (MAX(V) FOR R IN(1, 2, 3, 4, 5));

方法二：MAX+DECODE

SELECT COL, MAX(DECODE(R, 1, V)), MAX(DECODE(R, 2, V)), MAX(DECODE(R, 3, V)),
       MAX(DECODE(R, 4, V)), MAX(DECODE(R, 5, V))
FROM   (SELECT T.R, DECODE(T1.L, 1, 'A', 2, 'B') AS COL,
                DECODE(T1.L, 1, A, 2, B) V
         FROM   (SELECT ROW_NUMBER() OVER(ORDER BY A, B) AS R, A, B
                  FROM   TMP) T,
                (SELECT LEVEL L
                  FROM   DUAL
                  CONNECT BY LEVEL <= 2) T1)
GROUP  BY COL

方法三：MODEL子句 数据库10g以上

SELECT DECODE(R, 1, 'A', 'B') AS COL, V1, V2, V3, V4, V5
FROM   TMP
MODEL
RETURN UPDATED ROWS
DIMENSION BY(ROW_NUMBER()OVER(ORDER BY A,B) AS R)
MEASURES (A,B,A AS V1,A AS V2,A AS V3,A AS V4,A AS V5)
RULES
(
V1[R<=2]=DECODE(CV(R),1,A[1],2,B[1]), 
V2[R<=2]=DECODE(CV(R),1,A[2],2,B[2]), 
V3[R<=2]=DECODE(CV(R),1,A[3],2,B[3]), 
V4[R<=2]=DECODE(CV(R),1,A[4],2,B[4]), 
V5[R<=2]=DECODE(CV(R),1,A[5],2,B[5])
)

 需求：表2转置成表1

测试数据：

CREATE TABLE TMP1 AS 
SELECT 'A' AS COL,'A1' AS V1,'A2' AS V2,'A3' AS V3,'A4' AS V4,'A5' AS V5 FROM DUAL
UNION ALL
SELECT 'B','B1','B2','B3','B4','B5' FROM DUAL 

方法一：UNPIVOT+PIVOT  数据库11g以上，上面的方法反过来用

SELECT A, B
FROM   (SELECT *
         FROM   TMP1 UNPIVOT(V FOR COL1 IN(V1, V2, V3, V4, V5)))
PIVOT(MAX(V)
FOR    COL IN('A' AS A, 'B' AS B))
ORDER  BY A, B

方法二：MAX+DECODE

SELECT MAX(DECODE(COL, 'A', V)) AS A, MAX(DECODE(COL, 'B', V)) AS B
FROM   (SELECT COL, L, DECODE(L, 1, V1, 2, V2, 3, V3, 4, V4, 5, V5) AS V
         FROM   TMP1,
                (SELECT LEVEL AS L
                  FROM   DUAL
                  CONNECT BY LEVEL <= 5))
GROUP  BY L
ORDER  BY A, B

方法三：MODEL子句 数据库10g以上

SELECT A, B
FROM   TMP1
MODEL
RETURN UPDATED ROWS
DIMENSION BY(ROW_NUMBER()OVER(ORDER BY COL) AS R)
MEASURES (V1,V2,V3,V4,V5,V1 AS A,V2 AS B)
RULES ITERATE(5)
(
A[ITERATION_NUMBER+1]=DECODE(CV(R),1,V1[1],2,V2[1],3,V3[1],4,V4[1],V5[1]),
B[ITERATION_NUMBER+1]=DECODE(CV(R),1,V1[2],2,V2[2],3,V3[2],4,V4[2],V5[2])
)