在甲骨文 10g 上枢轴

Question

我正在使用oracle 10g。我有一个临时表温度。

TEMP具有以下结构：-

USER COUNT TYPE
---- ----- ----
   1    10   T1
   2    21   T2
   3    45   T1
   1     7   T1
   2     1   T3

我需要一个查询，它将显示所有类型都有列名，并且类型可以具有任何值，例如T1, T2,..Tn，结果将如下所示：-

USER T1 T2 T3
---- -- -- --
   1 17  0  0
   2  0 21  1
   3 45  0  0

和 用户 列将显示所有用户，T1, T2列将显示类型总数。

Answer 1

在 Oracle 10g 中，没有PIVOT函数，但您可以使用具有CASE的聚合来复制它：

select usr,
  sum(case when tp ='T1' then cnt else 0 end) T1,
  sum(case when tp ='T2' then cnt else 0 end) T2,
  sum(case when tp ='T3' then cnt else 0 end) T3
from temp
group by usr;

请参阅带有演示的 SQL 小提琴

如果你有Oracle 11g+，那么你可以使用PIVOT函数：

select *
from temp
pivot
(
  sum(cnt)
  for tp in ('T1', 'T2', 'T3')
) piv

请参阅带有演示的 SQL 小提琴

如果要转换的值数量未知，则可以创建一个过程来生成此值的动态版本：

CREATE OR REPLACE procedure dynamic_pivot(p_cursor in out sys_refcursor)
as
    sql_query varchar2(1000) := 'select usr ';
    begin
        for x in (select distinct tp from temp order by 1)
        loop
            sql_query := sql_query ||
              ' , sum(case when tp = '''||x.tp||''' then cnt else 0 end) as '||x.tp;
                dbms_output.put_line(sql_query);
        end loop;
        sql_query := sql_query || ' from temp group by usr';
        open p_cursor for sql_query;
    end;
/

然后执行代码：

variable x refcursor
exec dynamic_pivot(:x)
print x

所有版本的结果都相同：

| USR | T1 | T2 | T3 |
----------------------
|   1 | 17 |  0 |  0 |
|   2 |  0 | 21 |  1 |
|   3 | 45 |  0 |  0 |

编辑：根据您的注释，如果您想要一个Total字段，最简单的方法是将查询放在另一个SELECT中，如下所示：

select usr,
  T1 + T2 + T3 as Total,
  T1,
  T2,
  T3
from
(
  select usr,
    sum(case when tp ='T1' then cnt else 0 end) T1,
    sum(case when tp ='T2' then cnt else 0 end) T2,
    sum(case when tp ='T3' then cnt else 0 end) T3
  from temp
  group by usr
) src;

请参阅带有演示的 SQL 小提琴

Answer 2

下面是用于创建表的代码：

CREATE TABLE TBL_TEMP
(
    USR   NUMBER
   ,CNT   NUMBER
   ,TP    VARCHAR2 (10)
);
INSERT INTO TBL_TEMP VALUES (1,10,'T1');
INSERT INTO TBL_TEMP  VALUES (2,21,'T2');
INSERT INTO TBL_TEMP  VALUES (3,45,'T1');
INSERT INTO TBL_TEMP  VALUES (1,7,'T1');
INSERT INTO TBL_TEMP  VALUES (2,1,'T3');

并且，这是您请求的代码：

  SELECT T1.USR
        ,SUM (T1) T1
        ,SUM (T2) T2
        ,SUM (T3) T3
    FROM (SELECT DISTINCT USR FROM TBL_TEMP) T1
        ,(SELECT T2.USR
                ,DECODE (T2.TP, 'T1', T2.CNT, 0) T1
                ,DECODE (T2.TP, 'T2', T2.CNT, 0) T2
                ,DECODE (T2.TP, 'T3', T2.CNT, 0) T3
            FROM TBL_TEMP T2) T2
   WHERE T1.USR = T2.USR
GROUP BY T1.USR;

而且，结果就是您想要的。

在此处的SQL Fiddle Link中检查它

Answer 3

请检查查询：

SET SERVEROUTPUT ON;
DECLARE
CURSOR V_CUR IS
 SELECT DISTINCT "TYPE" FROM temp_tbl;
V_QUERY CLOB;
V_COLUMNHEADING CLOB;
V_ROW V_CUR%ROWTYPE;
BEGIN
  OPEN V_CUR;
  LOOP
  FETCH V_CUR INTO V_ROW;
  EXIT WHEN V_CUR%NOTFOUND;
    V_COLUMNHEADING:=CONCAT(V_COLUMNHEADING,''''||V_ROW."TYPE"||''' AS "'||V_ROW."TYPE"||'",');
  END LOOP;
  IF NVL(V_COLUMNHEADING,' ') <> ' ' THEN
        V_COLUMNHEADING := SUBSTR(V_COLUMNHEADING,0,LENGTH(V_COLUMNHEADING)-1);
  END IF;
  V_QUERY := CONCAT(V_QUERY,'select * from (select * from temp_tbl)a pivot (sum("COUNT")for "TYPE" in ('||V_COLUMNHEADING||'))b;');
  DBMS_OUTPUT.PUT_LINE(V_QUERY);
END ;

要选择数据，请使用语句 OPEN CUR_TURN_TIME FOR V_QUERY; 代替 DBMS_OUTPUT.PUT_LINE(V_QUERY); ，其中 CUR_TURN_TIME 是在包中声明的游标。