SQL Pivot with variable columns

Question

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

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

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'))

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.

Solution

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() requires 11gR2, but since you're using PIVOT, I guess you're using it anyway.