Pivoting rows into columns dynamically in Oracle

Question

I have the following Oracle 10g table called _kv:

select * from _kv

ID       K       V
----     -----   -----
  1      name    Bob
  1      age     30
  1      gender  male
  2      name    Susan
  2      status  married

I'd like to turn my keys into columns using plain SQL (not PL/SQL) so that the resulting table would look something like this:

ID       NAME    AGE    GENDER  STATUS
----     -----   -----  ------  --------
  1      Bob      30     male 
  2      Susan                   married

• The query should have as many columns as unique Ks exist in the table (there aren't that many)
• There's no way to know what columns may exist before running the query.
• I'm trying to avoid running an initial query to programatically build the final query.
• The blank cells may be nulls or empty strings, doesn't really matter.
• I'm using Oracle 10g, but an 11g solution would also be ok.

There are a plenty of examples out there for when you know what your pivoted columns may be called, but I just can't find a generic pivoting solution for Oracle.

Thanks!

Answer 1

Oracle 11g provides a PIVOT operation that does what you want.

Oracle 11g solution

select * from
(select id, k, v from _kv) 
pivot(max(v) for k in ('name', 'age', 'gender', 'status')

(Note: I do not have a copy of 11g to test this on so I have not verified its functionality)

I obtained this solution from: http://orafaq.com/wiki/PIVOT

EDIT -- pivot xml option (also Oracle 11g)
Apparently there is also a pivot xml option for when you do not know all the possible column headings that you may need. (see the XML TYPE section near the bottom of the page located at http://www.oracle.com/technetwork/articles/sql/11g-pivot-097235.html)

select * from
(select id, k, v from _kv) 
pivot xml (max(v)
for k in (any) )

(Note: As before I do not have a copy of 11g to test this on so I have not verified its functionality)

Edit2: Changed v in the pivot and pivot xml statements to max(v) since it is supposed to be aggregated as mentioned in one of the comments. I also added the in clause which is not optional for pivot. Of course, having to specify the values in the in clause defeats the goal of having a completely dynamic pivot/crosstab query as was the desire of this question's poster.

Answer 2

To deal with situations where there are a possibility of multiple values (v in your example), I use PIVOT and LISTAGG:

SELECT * FROM
(
  SELECT id, k, v
  FROM _kv 
)
PIVOT 
(
  LISTAGG(v ,',') 
  WITHIN GROUP (ORDER BY k) 
  FOR k IN ('name', 'age','gender','status')
)
ORDER BY id;

Since you want dynamic values, use dynamic SQL and pass in the values determined by running a select on the table data before calling the pivot statement.

Answer 3

Happen to have a task on pivot. Below works for me as tested just now on 11g:

select * from
(
  select ID, COUNTRY_NAME, TOTAL_COUNT from ONE_TABLE 
) 
pivot(
  SUM(TOTAL_COUNT) for COUNTRY_NAME in (
    'Canada', 'USA', 'Mexico'
  )
);

Answer 4

First of all, dynamically pivot using pivot xml again needs to be parsed. We have another way of doing this by storing the column names in a variable and passing them in the dynamic sql as below.

Consider we have a table like below.

If we need to show the values in the column YR as column names and the values in those columns from QTY, then we can use the below code.

declare
  sqlqry clob;
  cols clob;
begin
  select listagg('''' || YR || ''' as "' || YR || '"', ',') within group (order by YR)
  into   cols
  from   (select distinct YR from EMPLOYEE);


  sqlqry :=
  '      
  select * from
  (
      select *
      from EMPLOYEE
  )
  pivot
  (
    MIN(QTY) for YR in (' || cols  || ')
  )';

  execute immediate sqlqry;
end;
/

RESULT