oracle 10g如何将列转换为行

2022-01-22 00:00:00 pivot sql oracle oracle10g

假设我有一个 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"

相关文章