Oracle使用SQL实现矩阵转置

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])
)

 

Oracle使用SQL实现矩阵转置

标签：for   技术   tmp   nbsp   11g   pivot   date   nio   测试