具有可变列的 SQL Pivot

Question

我有两个表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      12345P1          phone2      23455P2          phone1      67897P2          phone3      89786

我需要创建一个看起来像这样的 View v_pc

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

即，联系人表的行被旋转以形成 View 的列(列数将根据“phone_types”列的不同值而变化)。

有什么办法可以旋转联系人表，但使用动态的 pivot-in-clause，比如

SELECT * FROM (    SELECT        person_idd,        phone_type,        phone_no    FROM contacts ) PIVOT (MAX(phone_no) FOR phone_type IN ('phone1','phone2','phone3'))

我还尝试将 XML 子句与 pivot 一起使用，因此使用动态 pivot-in-clause，即以 XML 格式提取结果，然后使用 XMLTABLE 重新创建列。但是我无法达到预期的结果。

Answer 1

您可以使用动态 SQL(假设您有一个类型为 SYS_REFCURSOR 的绑定(bind)变量 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() 需要 11gR2，但由于您使用的是 PIVOT，我猜您无论如何都在使用它。