将数据表的列转为行的技术方法
柯锐(湖北省红安县审计局)
在日常审计工作经常遇到各种各样类型的数据表,由于必须达到规范化的要求,需要将数据表的列转为行,下面借助一个实例从多个角度实现这项技术方法,实例数据表的内容如下:
方法一:使用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实现列转行
(柯锐)
方法一:使用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实现列转行
(柯锐)
【关闭】 【打印】 |