oracle 带有可变列的 SQL Pivot
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/12069992/
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
SQL Pivot with variable columns
提问by Aditya Jain
I have two tables say Person(person_id, name) and another table Contacts(person_id, phone_type, phone_no).
我有两个表说Person(person_id, name) 和另一个表Contacts(person_id, phone_type, phone_no)。
Person ----------------- person_id name ----------------- P1 Abc P2 Xyz
Contacts -------------------------------- person_id phone_type phone_no -------------------------------- P1 phone1 12345 P1 phone2 23455 P2 phone1 67897 P2 phone3 89786
I need to create a view v_pc which looks something like
我需要创建一个视图 v_pc，它看起来像
v_pc person_id name phone1 phone2 phone3 ------------------------------------- P1 Abc 12345 23455 P2 Xyz 67897 89786
i.e., rows of contacts table are pivot-ed to form columns for the view(number of columns will be variable based on distinct values of 'phone_types' column).
Is there any way I can Pivot the contacts table but use dynamic pivot-in-clause, something like
即，联系人表的行被旋转以形成视图的列（列数将根据“phone_types”列的不同值而变化）。
有什么办法可以旋转联系人表，但使用动态数据透视子句，例如
SELECT *
FROM (
SELECT
person_idd,
phone_type,
phone_no
FROM contacts
) PIVOT (MAX(phone_no) FOR phone_type IN ('phone1','phone2','phone3'))
我还尝试在数据透视中使用 XML 子句，因此使用动态数据透视子句，即，在 XML 中提取结果，然后使用 XMLTABLE 重新创建列。但我无法达到预期的结果。
回答by Frank Schmitt
You can use dynamic SQL (this assumes you have a bind variable v_cur of type SYS_REFCURSOR):
您可以使用动态 SQL（假设您有一个 SYS_REFCURSOR 类型的绑定变量 v_cur）：
declare
v_sql varchar2(32000);
v_phonetypes varchar2(32000);
begin
-- base SQL statement
v_sql := 'SELECT *
FROM (
SELECT
person_id,
phone_type,
phone_no
FROM contacts
) PIVOT (MAX(phone_no) FOR phone_type IN (';
-- comma-separated list of phone types
select
listagg('''' || phone_type || '''', ',') within group (order by phone_type)
into v_phonetypes
from (select distinct phone_type from contacts);
v_sql := v_sql || v_phonetypes || '))';
dbms_output.put_line(v_sql);
-- execute query
open :v_cur for v_sql;
end;
LISTAGG() requires 11gR2, but since you're using PIVOT, I guess you're using it anyway.
LISTAGG() 需要 11gR2，但由于您使用的是 PIVOT，我猜您无论如何都会使用它。
回答by Chitharanjan Das
Why don't you try creating the view using dynamic SQL? Just engineer the CREATE statement in the same manner that Frank has created the PIVOT query above. Then execute both, CREATE statement first, PIVOT query later.
为什么不尝试使用动态 SQL 创建视图？只需按照 Frank 创建上述 PIVOT 查询的相同方式设计 CREATE 语句即可。然后执行两者，先 CREATE 语句，后 PIVOT 查询。
