SQL 以 Oracle 10g 为中心
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/13410464/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Pivot on Oracle 10g
提问by F11
I am using oracle 10g. I have a temp table TEMP.
我正在使用 oracle 10g。我有一个临时表 TEMP。
TEMP has following structure:-
TEMP 具有以下结构：-
USER COUNT TYPE
---- ----- ----
1 10 T1
2 21 T2
3 45 T1
1 7 T1
2 1 T3
I need a query which will show all types has column names,and types can have any value like T1, T2,..Tnand result will be like:-
我需要一个查询来显示所有类型都具有列名，并且类型可以具有任何值，例如T1, T2,..Tn，结果如下：-
USER T1 T2 T3
---- -- -- --
1 17 0 0
2 0 21 1
3 45 0 0
and User column will show all the users and T1, T2column will show total count of types.
用户列将显示所有用户，T1, T2列将显示类型总数。
回答by Taryn
In Oracle 10g, there was no PIVOTfunction but you can replicate it using an aggregate with a CASE:
在 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;
If you have Oracle 11g+ then you can use the PIVOTfunction:
如果您有 Oracle 11g+，那么您可以使用该PIVOT功能：
select *
from temp
pivot
(
sum(cnt)
for tp in ('T1', 'T2', 'T3')
) piv
If you have an unknown number of values to transform, then you can create a procedure to generate a dynamic version of this:
如果要转换的值数量未知，则可以创建一个过程来生成此动态版本：
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;
/
then to execute the code:
然后执行代码：
variable x refcursor
exec dynamic_pivot(:x)
print x
The result for all versions is the same:
所有版本的结果都是一样的：
| USR | T1 | T2 | T3 |
----------------------
| 1 | 17 | 0 | 0 |
| 2 | 0 | 21 | 1 |
| 3 | 45 | 0 | 0 |
Edit: Based on your comment if you want a Totalfield, the easiest way is to place the query inside of another SELECTsimilar to this:
编辑：根据您的评论，如果您想要一个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;
回答by kangbu
Here is a code for table creation:
这是创建表的代码：
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');
And, here is a code for your request:
而且，这是您的请求的代码：
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;
And, the result is what you wanted.
而且，结果就是你想要的。
Check it at SQL Fiddle Link here
在此处查看 SQL Fiddle Link
回答by TechDo
Please check the query:
请检查查询：
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 ;
To select data, use statement OPEN CUR_TURN_TIME FOR V_QUERY;in place of DBMS_OUTPUT.PUT_LINE(V_QUERY);, where CUR_TURN_TIMEis the cursor declared in the package.
要选择数据，请使用 statementOPEN CUR_TURN_TIME FOR V_QUERY;代替DBMS_OUTPUT.PUT_LINE(V_QUERY);，其中CUR_TURN_TIME在包中声明了游标。
