带变量列的SQL Pivot [英] SQL Pivot with variable columns

问题描述

我有两个表，分别是 Person (人员)，(person_id，姓名)和另一个表 Contacts (联系人)(person_id，phone_type，phone_no).

I have two tables say Person(person_id, name) and another table 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

我需要创建一个外观类似的视图v_pc

I need to create a view v_pc which looks something like

v_pc
person_id  name phone1 phone2 phone3
-------------------------------------
P1         Abc  12345  23455  
P2         Xyz  67897         89786

即，联系人表的行被旋转以形成视图的列(列数将根据'phone_types'列的不同值而变化).

有什么办法可以枢纽化联系人表，但可以使用动态子句式，例如

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

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重新创建列.但是我无法达到预期的结果.

I also tried using XML clause in with pivot so use dynamic pivot-in-clause ,i.e., extracting result in XML and then recreating columns using XMLTABLE. But I am not able to reach the desired result.

推荐答案

您可以使用动态SQL(假设您具有类型为SYS_REFCURSOR的绑定变量v_cur):

You can use dynamic SQL (this assumes you have a bind variable v_cur of type SYS_REFCURSOR):

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()需要11gR2，但由于您使用的是PIVOT，所以我想您还是在使用它.

LISTAGG() requires 11gR2, but since you're using PIVOT, I guess you're using it anyway.

这篇关于带变量列的SQL Pivot的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！