oracle行列转换

 

1) 列转行

WITH TB AS 
(
    SELECT '1'   AS SID,
            '英语'  AS E,
            '70' AS E_SCORE,
            '数学' S,
            '89' AS S_SCORE,
            '语文'  AS T,
            '69' AS T_SCORE
     FROM   DUAL       
)
SELECT SID,E AS 科目,E_SCORE AS 分数
FROM
(
    SELECT SID,E,E_SCORE  FROM  TB
    UNION ALL
    SELECT SID,S,S_SCORE  FROM  TB
    UNION ALL
    SELECT SID,T,T_SCORE  FROM  TB
);

 

2) 行转列 (数值类型)

 

WITH TB AS
(
    SELECT '1'  AS SID, '英语'  AS E,'70' AS E_SCORE FROM DUAL
    UNION ALL
    SELECT '1'  AS SID, '数学' S,'89' AS S_SCORE  FROM DUAL
    UNION ALL
    SELECT '1'  AS SID, '语文'  AS T,'69' AS T_SCORE  FROM DUAL
    UNION ALL
    SELECT '2'  AS SID, '英语'  AS E,'72' AS E_SCORE FROM DUAL
    UNION ALL
    SELECT '2'  AS SID, '数学' S,'58' AS S_SCORE  FROM DUAL
    UNION ALL
    SELECT '2'  AS SID, '语文'  AS T,'60' AS T_SCORE  FROM DUAL
    UNION ALL
    SELECT '3'  AS SID, '英语'  AS E,'85' AS E_SCORE FROM DUAL
    UNION ALL
    SELECT '3'  AS SID, '数学' S,'90' AS S_SCORE  FROM DUAL
)
SELECT SID,
       SUM(DECODE(E,'英语',E_SCORE)) AS 英语,
       SUM(DECODE(E,'语文',E_SCORE)) AS 语文,
       SUM(DECODE(E,'数学',E_SCORE)) AS 数学
FROM   TB
GROUP BY SID;

 

3) 方法一:行转列 (字符串类型，适用于数值类型)

 

WITH TB AS
(
    SELECT 1 AS ID,'你' AS NAME FROM DUAL
    UNION ALL
    SELECT 1 ,'好'  FROM DUAL
    UNION ALL
    SELECT 1 ,'吗'  FROM DUAL
    UNION ALL
    SELECT 2 ,'我'  FROM DUAL
    UNION ALL
    SELECT 2 ,'不'  FROM DUAL
    UNION ALL
    SELECT 2 ,'好'  FROM DUAL
),
TB2 AS
(
    SELECT ID,
           NAME,
           ROW_NUMBER() OVER(PARTITION BY ID ORDER BY ID) AS RN
    FROM   TB
),
TB3 AS
(
    SELECT ID,
           MAX(SYS_CONNECT_BY_PATH(NAME,' ')) AS NAME
    FROM   TB2
    START WITH RN = 1
    CONNECT BY PRIOR RN = RN - 1 AND PRIOR ID = ID
    GROUP BY ID
)
SELECT ID,REPLACE(NAME,' ','') AS NAME
FROM   TB3;

 

4) 方法二:行转列 (字符串类型，适用于数值类型)

 

WITH TB AS
(
    SELECT 1 AS ID,'你' AS NAME FROM DUAL
    UNION ALL
    SELECT 1 ,'好'  FROM DUAL
    UNION ALL
    SELECT 1 ,'吗'  FROM DUAL
    UNION ALL
    SELECT 2 ,'我'  FROM DUAL
    UNION ALL
    SELECT 2 ,'不'  FROM DUAL
    UNION ALL
    SELECT 2 ,'好'  FROM DUAL
),
TB2 AS
(
    SELECT ID,
           NAME,
           RN,
           LAG(RN) OVER(PARTITION BY ID ORDER BY RN) AS LG
    FROM   (
                SELECT ID,NAME,ROW_NUMBER() OVER(PARTITION BY ID ORDER BY ID) AS RN
                FROM   TB
           ) 
),
TB3 AS
(
    SELECT ID,
           MAX(SYS_CONNECT_BY_PATH(NAME,' ')) AS NAME
    FROM   TB2
    START WITH RN = 1
    CONNECT BY PRIOR RN = RN - 1 AND PRIOR ID = ID
    GROUP BY ID
)
SELECT ID,REPLACE(NAME,' ','') AS NAME
FROM   TB3;