Oracle SQL 数据透视查询

Question

我的表中有数据，如下所示：

MONTH VALUE

1     100
2     200
3     300
4     400
5     500
6     600

我想编写一个 SQL 查询，结果如下：

MONTH_JAN MONTH_FEB MONTH_MAR MONTH_APR MONTH_MAY MONTH_JUN
100       200       300       400       500       600

Answer 1

Oracle 11g 及更高版本

从 Oracle 11g 开始，您现在可以使用 PIVOT 运算符来实现该结果：

create table tq84_pivot (
  month number,
  value number
);

insert into tq84_pivot values(1, 100);
insert into tq84_pivot values(2, 200);
insert into tq84_pivot values(3, 300);
insert into tq84_pivot values(4, 400);
insert into tq84_pivot values(5, 500);
insert into tq84_pivot values(6, 600);
--
insert into tq84_pivot values(1, 400);
insert into tq84_pivot values(2, 350);
insert into tq84_pivot values(4, 150);

select 
  *
from
  tq84_pivot
pivot (
   sum (value) as sum_value for
     (month) in (1 as month_jan,
                 2 as month_feb,
                 3 as month_mar,
                 4 as month_apr,
                 5 as month_mai,
                 6 as month_jun,
                 7 as month_jul,
                 8 as month_aug,
                 9 as month_sep,
                10 as month_oct,
                11 as month_nov,
                12 as month_dec)
);

Answer 2

Oracle 9i+ 支持：

SELECT SUM(CASE WHEN t.month = 1 THEN t.value ELSE 0 END) AS JAN,
       SUM(CASE WHEN t.month = 2 THEN t.value ELSE 0 END) AS FEB,
       SUM(CASE WHEN t.month = 3 THEN t.value ELSE 0 END) AS MAR,
       SUM(CASE WHEN t.month = 4 THEN t.value ELSE 0 END) AS APR,
       SUM(CASE WHEN t.month = 5 THEN t.value ELSE 0 END) AS MAY,
       SUM(CASE WHEN t.month = 6 THEN t.value ELSE 0 END) AS JUN
  FROM YOUR_TABLE t

您只列出两列——类似这样的内容可能应该按年份分组。

有 ANSI PIVOT（和 UNPIVOT）语法，但 Oracle 直到 11g 才支持它。在 9i 之前，您必须用 Oracle 特定的 DECODE 替换 CASE 语句。

Answer 3

Oracle 11g+的动态透视

Oracle 的 SQL 中没有直接的动态透视方法，除非它返回 XML 类型结果。

对于非 XML 结果，可以通过创建 SYS_REFCURSOR 返回类型的函数来使用 PL/SQL

• 带有PIVOT子句

  创建或替换函数 Get_Month_Values RETURN SYS_REFCURSOR IS
     v_recordset SYS_REFCURSOR；
     v_sql VARCHAR2(32767);
     v_cols VARCHAR2(32767);
  开始
     SELECT LISTAGG( ''''||月||''' AS "MONTH_'||TO_CHAR( TO_DATE(月,'mm') ,'MON')||'"' , ',' )
                    组内（按月排序）
       INTO v_cols
       来自选项卡；
  
     v_sql :='选择 *
                FROM 选项卡
               枢
               （
                MAX(值) FOR 月份 IN ( '|| v_cols ||' )
               )';
  
     打开 v_recordset FOR v_sql；
     DBMS_OUTPUT.PUT_LINE(v_sql);
     返回 v_recordset;
  结尾;
  /
  

• 带有条件聚合

  创建或替换函数 Get_Month_Values RETURN SYS_REFCURSOR IS
     v_recordset SYS_REFCURSOR；
     v_sql VARCHAR2(32767);
     v_cols VARCHAR2(32767);
  开始
     SELECT LISTAGG('MAX( CASE WHEN 月份 = '''||月||''' THEN '||值||' END ) AS "MONTH_'||TO_CHAR( TO_DATE(月,'mm') ,'MON ')||'"' , ',' )
                    组内（按月排序）                 
       INTO v_cols
       来自选项卡；
  
     v_sql :='选择'|| v_cols ||'从选项卡';
  
     打开 v_recordset FOR v_sql；
     DBMS_OUTPUT.PUT_LINE(v_sql);
     返回 v_recordset;
  结尾;
  /
  

调用该函数

VAR rc REFCURSOR
EXEC :rc := Get_Month_Values;
PRINT rc

，然后可以从SQL Developer的命令行

演示

Answer 4

如何获取表的枢轴

SELECT *
FROM Product
PIVOT (MAX(VALUE) FOR (P_name, P_id) IN ((choclate,6) AS choclate_6, (candies,8) AS candies_8, (Biscuits,9) AS Biscuits_9)