oracle 10g如何将列转换为行
假设我有一个 Oracle sql 查询的结果:
Suppose I have the result of an Oracle sql query:
Month Date
----- -----
Jan 10
Jan 15
Jan 20
Feb 11
Feb 16
Feb 25
我想以以下格式显示此数据:
I want to display this data in the following format:
Jan Jan Jan Feb Feb Feb
10 15 20 11 16 25
如何编写查询?
推荐答案
使用PIVOT:
SQL> WITH sample_data AS(
2 SELECT 'Jan' mnth, 10 dt FROM dual UNION ALL
3 SELECT 'Jan' mnth, 15 dt FROM dual UNION ALL
4 SELECT 'Jan' mnth, 20 dt FROM dual UNION ALL
5 SELECT 'Feb' mnth, 11 dt FROM dual UNION ALL
6 SELECT 'Feb' mnth, 16 dt FROM dual UNION ALL
7 SELECT 'Feb' mnth, 25 dt FROM dual
8 )
9 -- end of smaple_data mimicking real table
10 SELECT *
11 FROM
12 (SELECT dt, row_number() OVER(ORDER BY NULL) rn FROM sample_data
13 ) PIVOT (MAX(dt) FOR (rn)
14 IN (1 AS Jan_1, 2 AS jan_2, 3 AS Jan_3, 4 AS Feb_1, 5 Feb_2, 6 Feb_3));
JAN_1 JAN_2 JAN_3 FEB_1 FEB_2 FEB_3
---------- ---------- ---------- ---------- ---------- ----------
10 15 20 11 16 25
幕后 PIVOT是相同的MAX + CASE.您可以在 12c
中查看它,Oracle 将 EXPAND_SQL_TEXT 过程添加到 DBMS_UTILITY 包中.
Under the hood PIVOT is same MAX + CASE. You can check it in 12c
where Oracle added EXPAND_SQL_TEXT procedure to DBMS_UTILITY package.
SQL> VARIABLE c CLOB
SQL> BEGIN
2 dbms_utility.expand_sql_text(Q'[WITH sample_data AS(
3 SELECT 'Jan' mnth, 10 dt FROM dual UNION ALL
4 SELECT 'Jan' mnth, 15 dt FROM dual UNION ALL
5 SELECT 'Jan' mnth, 20 dt FROM dual UNION ALL
6 SELECT 'Feb' mnth, 11 dt FROM dual UNION ALL
7 SELECT 'Feb' mnth, 16 dt FROM dual UNION ALL
8 SELECT 'Feb' mnth, 25 dt FROM dual
9 )
10 -- end of smaple_data mimicking real table
11 SELECT *
12 FROM
13 (SELECT dt, row_number() OVER(ORDER BY NULL) rn FROM sample_data
14 ) PIVOT (MAX(dt) FOR (rn)
15 IN (1 AS Jan_1, 2 AS jan_2, 3 AS Jan_3, 4 AS Feb_1, 5 Feb_2, 6 Feb_3))]',:c);
16 END;
17 /
PL/SQL procedure successfully completed.
现在让我们看看 Oracle 在内部实际做了什么:
SQL> set long 100000
SQL> print c
C
--------------------------------------------------------------------------------
SELECT "A1"."JAN_1" "JAN_1",
"A1"."JAN_2" "JAN_2",
"A1"."JAN_3" "JAN_3",
"A1"."FEB_1" "FEB_1",
"A1"."FEB_2" "FEB_2",
"A1"."FEB_3" "FEB_3"
FROM
(SELECT MAX(
CASE WHE N ("A2"."RN"=1)
THEN "A2"."DT"
END ) "JAN_1",
MAX(
CASE
WHEN ("A2"."RN"=2)
THEN " A2"."DT"
END ) "JAN_2",
MAX(
CASE
WHEN ("A2"."RN"=3)
THEN "A2"."DT"
END ) "JAN_3" ,
MAX(
CASE
WHEN ("A2"."RN"=4)
THEN "A2"."DT"
END ) "FEB_1",
MAX(
CASE
WHEN ("A2". "RN"=5)
THEN "A2"."DT"
END ) "FEB_2",
MAX(
CASE
WHEN ("A2"."RN"=6)
THEN "A2"."DT"
END ) "FEB_3"
FROM
(SELECT "A3"."DT" "DT",
ROW_NUMBER() OVER ( ORDER BY NULL) " RN"
FROM (
(SELECT 'Jan' "MNTH",10 "DT" FROM "SYS"."DUAL" "A10"
)
UNION ALL (SE LECT 'Jan' "MNTH",15 "DT" FROM "SYS"."DUAL" "A9")
UNION ALL
(SELECT 'Jan' "MNTH",20 "DT" FROM "SYS"."DUAL" "A8"
)
UNION ALL
(SELECT 'Feb' "MNTH",11 "DT" FROM " SYS"."DUAL" "A7"
)
UNION ALL
(SELECT 'Feb' "MNTH",16 "DT" FROM "SYS"."DUAL" "A6"
)
UNION ALL
(SELECT 'Feb' "MNTH",25 "DT" FROM "SYS"."DUAL" "A5"
)) "A3"
) "A2"
) " A1"
相关文章