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.