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.

Yes, problem seems the same. However I require a solution in Oracle. — Aditya Jain
– Aditya Jain, Commented Aug 22, 2012 at 9:30

Frank Schmitt · Accepted Answer · 2012-08-22 10:00:16Z

2

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.

answered Aug 22, 2012 at 10:00

Frank Schmitt

30.5k13 gold badges79 silver badges111 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Aditya Jain Over a year ago

Thanks, but since I need the result as a View...dynamic query will not be helpful.

Frank Schmitt Over a year ago

AFAIK, this cannot be done (directly) with a view, since the column set of a view must bet static. You could of course re-create the view every time with 'EXECUTE IMMEDIATE', but that's a terrible hack (although sometimes necessary).

Chitharanjan Das · Accepted Answer · 2013-02-06 07:39:18Z

0

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.

answered Feb 6, 2013 at 7:39

Chitharanjan Das

1,35310 silver badges15 bronze badges

Collectives™ on Stack Overflow

SQL Pivot with variable columns

2 Answers 2

2 Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related