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!

Edit
Report