SQL oracle sql中的动态枢轴
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/15491661/
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
Dynamic pivot in oracle sql
提问by prabhakar
... pivot (sum(A) for B in (X))
... 枢轴 (sum(A) for B in (X))
Now B is of datatype varchar2 and X is a string of varchar2 values separated by commas.
Values for X are select distinct values from a column(say CL) of same table. This way pivot query was working.
现在 B 是 varchar2 数据类型，X 是由逗号分隔的 varchar2 值字符串。
X 的值是从同一表的列（例如 CL）中选择不同的值。这样数据透视查询就起作用了。
But the problem is that whenever there is a new value in column CL I have to manually add that to the string X.
但问题是，每当 CL 列中有新值时，我都必须手动将其添加到字符串 X 中。
I tried replacing X with select distinct values from CL. But query is not running.
The reason I felt was due to the fact that for replacing X we need values separated by commas.
Then i created a function to return exact output to match with string X. But query still doesn't run.
The error messages shown are like "missing righr parantheses", "end of file communication channel" etc etc.
I tried pivot xml instead of just pivot, the query runs but gives vlaues like oraxxx etc which are no values at all.
我尝试用从 CL 中选择不同的值替换 X。但是查询没有运行。
我觉得的原因是因为为了替换 X 我们需要用逗号分隔的值。
然后我创建了一个函数来返回与字符串 X 匹配的精确输出。但查询仍然没有运行。
显示的错误消息类似于“缺少正确的括号”、“文件通信通道结束”等。
我尝试使用pivot xml 而不仅仅是pivot，查询运行但给出了诸如oraxxx 等根本没有值的vlaues。
Maybe I am not using it properly.
Can you tell me some method to create a pivot with dynamic values?
也许我没有正确使用它。
你能告诉我一些用动态值创建枢轴的方法吗？
采纳答案by A.B.Cade
You can't put a non constant string in the INclause of the pivot clause.
You can use Pivot XML for that.
您不能IN在枢轴子句的子句中放置非常量字符串。
您可以为此使用 Pivot XML。
From documentation:
从文档：
subquery A subquery is used only in conjunction with the XML keyword. When you specify a subquery, all values found by the subquery are used for pivoting
subquery 子查询仅与 XML 关键字结合使用。指定子查询时，子查询找到的所有值都用于透视
It should look like this:
它应该是这样的：
select xmlserialize(content t.B_XML) from t_aa
pivot xml(
sum(A) for B in(any)
) t;
You can also have a subquery instead of the ANYkeyword:
您还可以使用子查询代替ANY关键字：
select xmlserialize(content t.B_XML) from t_aa
pivot xml(
sum(A) for B in (select cl from t_bb)
) t;
回答by user2179919
You cannot put a dynamic statement in the PIVOT's IN statement without using PIVOT XML, which outputs some less than desirable output. However, you can create an IN string and input it into your statement.
您不能在不使用 PIVOT XML 的情况下将动态语句放入 PIVOT 的 IN 语句中，这会输出一些不太理想的输出。但是，您可以创建一个 IN 字符串并将其输入到您的语句中。
First, here is my sample table;
首先，这是我的示例表；
myNumber myValue myLetter
---------- ---------- --------
1 2 A
1 4 B
2 6 C
2 8 A
2 10 B
3 12 C
3 14 A
First setup the string to use in your IN statement. Here you are putting the string into "str_in_statement". We are using COLUMN NEW_VALUEand LISTAGGto setup the string.
首先设置要在 IN 语句中使用的字符串。在这里，您将字符串放入“str_in_statement”中。我们使用COLUMN NEW_VALUE和LISTAGG来设置字符串。
clear columns
COLUMN temp_in_statement new_value str_in_statement
SELECT DISTINCT
LISTAGG('''' || myLetter || ''' AS ' || myLetter,',')
WITHIN GROUP (ORDER BY myLetter) AS temp_in_statement
FROM (SELECT DISTINCT myLetter FROM myTable);
Your string will look like:
您的字符串将如下所示：
'A' AS A,'B' AS B,'C' AS C
Now use the String statement in your PIVOT query.
现在在 PIVOT 查询中使用 String 语句。
SELECT * FROM
(SELECT myNumber, myLetter, myValue FROM myTable)
PIVOT (Sum(myValue) AS val FOR myLetter IN (&str_in_statement));
Here is the Output:
这是输出：
MYNUMBER A_VAL B_VAL C_VAL
---------- ---------- ---------- ----------
1 2 4
2 8 10 6
3 14 12
There are limitations though.You can only concatenate a string up to 4000 bytes.
不过也有限制。最多只能连接 4000 个字节的字符串。
回答by Scott
For later readers, here is another solution https://technology.amis.nl/2006/05/24/dynamic-sql-pivoting-stealing-antons-thunder/
对于后来的读者，这是另一个解决方案 https://technology.amis.nl/2006/05/24/dynamic-sql-pivoting-stealing-antons-thunder/
allowing a query like
允许像这样的查询
select * from table( pivot( 'select deptno, job, count(*) c from scott.emp group by deptno,job' ) )
回答by Rana Suhaib
USE DYNAMIC QUERY
使用动态查询
Test code is below
测试代码如下
-- DDL for Table TMP_TEST
--------------------------------------------------------
CREATE TABLE "TMP_TEST"
( "NAME" VARCHAR2(20),
"APP" VARCHAR2(20)
);
/
SET DEFINE OFF;
Insert into TMP_TEST (NAME,APP) values ('suhaib','2');
Insert into TMP_TEST (NAME,APP) values ('suhaib','1');
Insert into TMP_TEST (NAME,APP) values ('shahzad','3');
Insert into TMP_TEST (NAME,APP) values ('shahzad','2');
Insert into TMP_TEST (NAME,APP) values ('shahzad','5');
Insert into TMP_TEST (NAME,APP) values ('tariq','1');
Insert into TMP_TEST (NAME,APP) values ('tariq','2');
Insert into TMP_TEST (NAME,APP) values ('tariq','6');
Insert into TMP_TEST (NAME,APP) values ('tariq','4');
/
CREATE TABLE "TMP_TESTAPP"
( "APP" VARCHAR2(20)
);
SET DEFINE OFF;
Insert into TMP_TESTAPP (APP) values ('1');
Insert into TMP_TESTAPP (APP) values ('2');
Insert into TMP_TESTAPP (APP) values ('3');
Insert into TMP_TESTAPP (APP) values ('4');
Insert into TMP_TESTAPP (APP) values ('5');
Insert into TMP_TESTAPP (APP) values ('6');
/
create or replace PROCEDURE temp_test(
pcursor out sys_refcursor,
PRESULT OUT VARCHAR2
)
AS
V_VALUES VARCHAR2(4000);
V_QUERY VARCHAR2(4000);
BEGIN
PRESULT := 'Nothing';
-- concating activities name using comma, replace "'" with "''" because we will use it in dynamic query so "'" can effect query.
SELECT DISTINCT
LISTAGG('''' || REPLACE(APP,'''','''''') || '''',',')
WITHIN GROUP (ORDER BY APP) AS temp_in_statement
INTO V_VALUES
FROM (SELECT DISTINCT APP
FROM TMP_TESTAPP);
-- designing dynamic query
V_QUERY := 'select *
from ( select NAME,APP
from TMP_TEST )
pivot (count(*) for APP in
(' ||V_VALUES|| '))
order by NAME' ;
OPEN PCURSOR
FOR V_QUERY;
PRESULT := 'Success';
Exception
WHEN OTHERS THEN
PRESULT := SQLcode || ' - ' || SQLERRM;
END temp_test;
回答by user7144213
I used the above method (Anton PL/SQL custom function pivot()) and it done the job! As I am not a professional Oracle developer, these are simple steps I've done:
我使用了上面的方法（Anton PL/SQL 自定义函数 pivot()），它完成了工作！由于我不是专业的 Oracle 开发人员，这些是我完成的简单步骤：
1) Download the zip package to find pivotFun.sql in there. 2) Run once the pivotFun.sql to create a new function 3) Use the function in normal SQL.
1) 下载zip 包以在其中找到pivotFun.sql。2) 运行一次pivotFun.sql 以创建一个新函数 3) 在普通SQL 中使用该函数。
Just be careful with dynamic columns names. In my environment I found that column name is limited with 30 characters and cannot contain a single quote in it. So, my query is now something like this:
请注意动态列名称。在我的环境中，我发现列名限制为 30 个字符，并且其中不能包含单引号。所以，我的查询现在是这样的：
SELECT
*
FROM
table(
pivot('
SELECT DISTINCT
P.proj_id,
REPLACE(substr(T.UDF_TYPE_LABEL, 1, 30), '''''''','','') as Attribute,
CASE
WHEN V.udf_text is null and V.udf_date is null and V.udf_number is NOT null THEN to_char(V.udf_number)
WHEN V.udf_text is null and V.udf_date is NOT null and V.udf_number is null THEN to_char(V.udf_date)
WHEN V.udf_text is NOT null and V.udf_date is null and V.udf_number is null THEN V.udf_text
ELSE NULL END
AS VALUE
FROM
project P
LEFT JOIN UDFVALUE V ON P.proj_id = V.proj_id
LEFT JOIN UDFTYPE T ON V.UDF_TYPE_ID = T.UDF_TYPE_ID
WHERE
P.delete_session_id IS NULL AND
T.TABLE_NAME = ''PROJECT''
')
)
Works well with up to 1m records.
适用于最多 1m 的记录。
回答by Sarath Avanavu
I am not exactly going to give answer for the question OP has asked, instead I will be just describing how dynamic pivot can be done.
我不会完全回答 OP 提出的问题，相反，我将仅描述如何完成动态枢轴。
Here we have to use dynamic sql, by initially retrieving the column values into a variable and passing the variable inside dynamic sql.
这里我们必须使用动态 sql，首先将列值检索到一个变量中，然后将变量传递到动态 sql 中。
EXAMPLE
例子
Consider we have a table like below.
考虑我们有一个如下表。
If we need to show the values in the column YRas column names and the values in those columns from QTY, then we can use the below code.
如果我们需要将列中的值显示YR为列名以及来自 的那些列中的值QTY，那么我们可以使用以下代码。
declare
sqlqry clob;
cols clob;
begin
select listagg('''' || YR || ''' as "' || YR || '"', ',') within group (order by YR)
into cols
from (select distinct YR from EMPLOYEE);
sqlqry :=
'
select * from
(
select *
from EMPLOYEE
)
pivot
(
MIN(QTY) for YR in (' || cols || ')
)';
execute immediate sqlqry;
end;
/
RESULT
结果
If required, you can also create a temp table and do a select query in that temp table to see the results. Its simple, just add the CREATE TABLE TABLENAME ASin the above code.
如果需要，您还可以创建临时表并在该临时表中执行选择查询以查看结果。很简单，只需CREATE TABLE TABLENAME AS在上面的代码中添加。
sqlqry :=
'
CREATE TABLE TABLENAME AS
select * from
回答by Udara Kasun
You cannot puta dynamic statement in the PIVOT's IN statement without using PIVOT XML, but you can use small Technicto use dynamic statement in PIVOT. In PL/SQL, within a string value, two apostrophe is equal to one apostrophes.
如果不使用 PIVOT XML，就不能在 PIVOT 的 IN 语句中放置动态语句，但可以使用 small Technic在 PIVOT 中使用动态语句。在 PL/SQL 中，在一个字符串值中，两个撇号等于一个撇号。
declare
sqlqry clob;
search_ids varchar(256) := '''2016'',''2017'',''2018'',''2019''';
begin
search_ids := concat( search_ids,'''2020''' ); -- you can append new search id dynamically as you wanted
sqlqry :=
'
select * from
(
select *
from EMPLOYEE
)
pivot
(
MIN(QTY) for YR in (' || search_ids || ')
)';
execute immediate sqlqry;
end;
