在日常审计工作经常遇到各种各样类型的数据表,由于必须达到规范化的要求,需要将数据表的列转为行,下面借助一个实例从多个角度实现这项技术方法,实例数据表的内容如下: 方法一:使用union all 方法二:假设有张表只有一列LEL,保存1-10自然数列,可用下列方法生成: CREATE TABLE SEQ AS SELECT LEVEL LEL FROM DUAL CONNECT BY LEVEL <= 10; 然后用下列方法: WITH INFO AS ( SELECT CUST_NAME, "2009-08-01", "2009-08-02", "2009-08-03", LEL FROM orders_pivot,SEQ WHERE LEL <= 3 ORDER BY CUST_NAME,LEL ) SELECT i.CUST_NAME, CASE LEL WHEN 1 THEN DATE'2009-08-01' WHEN 2 THEN DATE'2009-08-02' WHEN 3 THEN DATE'2009-08-03' END ORDER_DATE, CASE LEL WHEN 1 THEN "2009-08-01" WHEN 2 THEN "2009-08-02" WHEN 3 THEN "2009-08-03" END AR FROM INFO I WHERE CASE LEL WHEN 1 THEN "2009-08-01" WHEN 2 THEN "2009-08-02" WHEN 3 THEN "2009-08-03" END IS NOT NULL; 结果如下图: 方法三:使用unpivot实现列转行 (柯锐)
|