【Oracle】Oracle 中的行列转换 （pivot 与 unpivot 语法）

【Oracle】pivot 与 unpivot 语法

• pivot – 行转列函数

  • 语法：

  pivot(聚合函数 FOR 需要转为列的值所在的列名 IN (需要转为列名的值)

  • 示例：
    测试数据

  SELECT 'zhangsan' STUDENT, '20190709' BIRTHDAY, 'CHINESE' SUBJECT, '90' MARK
    FROM DUAL
  UNION ALL
  SELECT 'zhangsan' STUDENT, '20190709' BIRTHDAY, 'MATH' SUBJECT, '98' MARK
    FROM DUAL
  UNION ALL
  SELECT 'zhangsan' STUDENT, '20190709' BIRTHDAY, 'ENGLISH' SUBJECT, '85' MARK
    FROM DUAL
  UNION ALL
  SELECT 'zhangsan' STUDENT, '20190709' BIRTHDAY, 'HISTORY' SUBJECT, '89' MARK
    FROM DUAL
  

  
  转换后

  WITH temp AS (
  SELECT 'zhangsan' STUDENT, '20190709' BIRTHDAY, 'CHINESE' SUBJECT, '90' MARK
    FROM DUAL
  UNION ALL
  SELECT 'zhangsan' STUDENT, '20190709' BIRTHDAY, 'MATH' SUBJECT, '98' MARK
    FROM DUAL
  UNION ALL
  SELECT 'zhangsan' STUDENT, '20190709' BIRTHDAY, 'ENGLISH' SUBJECT, '85' MARK
    FROM DUAL
  UNION ALL
  SELECT 'zhangsan' STUDENT, '20190709' BIRTHDAY, 'HISTORY' SUBJECT, '89' MARK
    FROM DUAL)
    
  SELECT * FROM temp pivot( MAX(MARK) FOR subject IN ('CHINESE', 'MATH', 'ENGLISH', 'HISTORY'))
  

  

• unpivot – 列转行函数

  • 语法：

  unpivot(转换后增加的列名 FOR 原列名转为行之后对应的列名 IN (需要转为行的列名)

  • 示例：
    测试数据

  SELECT 'zhangsan' STUDENT,
     '20190709' BIRTHDAY,
     '90' CHINESE,
     '98' MATH,
     '95' ENGLISH,
     '89' HISTORY
  FROM DUAL;
  

  
  转换后

  SELECT STUDENT, BIRTHDAY, SUBJECT, MARK
  FROM (SELECT 'zhangsan' STUDENT,
             '20190709' BIRTHDAY,
             '90' CHINESE,
             '98' MATH,
             '95' ENGLISH,
             '89' HISTORY
        FROM DUAL) UNPIVOT(MARK FOR SUBJECT IN(CHINESE,
                                            MATH,
                                            ENGLISH,
                                            HISTORY));
  

  

• unpivot – 列转行函数(高级用法 – 多列转行)

• 示例
  测试数据

  WITH tmp AS(
  SELECT 'LH' fac,'D5' Dong, '1F' flo, 1 D1,1 D2,0 D3, 1 F1, 1 F2, 1 F3, 1 S1, 1 S2, 1 S3 FROM Dual 
  UNION ALL
  SELECT 'LH' fac,'D5' Dong, '2F' flo, 1 D1,1 D2,0 D3, 1 F1, 1 F2, 1 F3, 1 S1, 1 S2, 1 S3 FROM Dual 
  UNION ALL
  SELECT 'GL' fac,'C4' Dong, '1F' flo, 1 D1,1 D2,0 D3, 1 F1, 1 F2, 1 F3, 1 S1, 1 S2, 1 S3 FROM Dual 
  UNION ALL
  SELECT 'GL' fac,'C4' Dong, '2F' flo, 1 D1,1 D2,0 D3, 1 F1, 1 F2, 1 F3, 1 S1, 1 S2, 1 S3 FROM Dual )
  SELECT * FROM  tmp;
  

  

  转换后

  WITH tmp AS(
  SELECT 'LH' fac,'D5' Dong, '1F' flo, 1 D1,1 D2,0 D3, 1 F1, 1 F2, 1 F3, 1 S1, 1 S2, 1 S3 FROM Dual 
  UNION ALL
  SELECT 'LH' fac,'D5' Dong, '2F' flo, 1 D1,1 D2,0 D3, 1 F1, 1 F2, 1 F3, 1 S1, 1 S2, 1 S3 FROM Dual 
  UNION ALL
  SELECT 'GL' fac,'C4' Dong, '1F' flo, 1 D1,1 D2,0 D3, 1 F1, 1 F2, 1 F3, 1 S1, 1 S2, 1 S3 FROM Dual 
  UNION ALL
  SELECT 'GL' fac,'C4' Dong, '2F' flo, 1 D1,1 D2,0 D3, 1 F1, 1 F2, 1 F3, 1 S1, 1 S2, 1 S3 FROM Dual )
  SELECT * FROM  tmp UNPIVOT((STATUS1, STATUS2, STATUS3) FOR TYP IN ((D1, D2, D3), (F1, F2, F3), (S1, S2, S3)));
  

  

该语法为 Oracle 11g 新语法，低版本 Oracle 使用会报错： ORA-00933：SQL command not properly ended

oracle 版本查询：
SELECT * FROM PRODUCT_COMPONENT_VERSION;