Pivoting rows into columns dynamically in Oracle

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.

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.

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