Pivoting rows into columns dynamically in Oracle

by

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.

More Related Contents:

  1. Merging two tables in SQL [closed]
  2. Dynamic Pivot in Oracle’s SQL
  3. Oracle SQL pivot query
  4. How can I combine multiple rows into a comma-delimited list in Oracle? [duplicate]
  5. Is it possible to output a SELECT statement from a PL/SQL block?
  6. Using ‘case expression column’ in where clause
  7. Oracle Joins – Comparison between conventional syntax VS ANSI Syntax
  8. Concatenate results from a SQL query in Oracle
  9. How to use BOOLEAN type in SELECT statement
  10. Dynamic Oracle Pivot_In_Clause
  11. How to kill a running SELECT statement
  12. Using pivot on multiple columns of an Oracle row
  13. Selecting the second row of a table using rownum
  14. Update with Join query in Oracle
  15. How to count the number of occurrences of a character in an Oracle varchar value?
  16. Oracle Dynamic Pivoting
  17. Different CURRENT_TIMESTAMP and SYSDATE in oracle
  18. How to convert Rows to Columns in Oracle? [duplicate]
  19. Advice Using Pivot Table in Oracle
  20. Oracle date “Between” Query
  21. ORA-01861: literal does not match format string
  22. Oracle Date TO_CHAR(‘Month DD, YYYY’) has extra spaces in it
  23. Query a Table’s Foreign Key relationships
  24. Is there an Oracle equivalent to SQL Server’s OUTPUT INSERTED.*?
  25. TSQL Pivot without aggregate function
  26. How to use a calculated column to calculate another column in the same view
  27. Alternatives to LIMIT and OFFSET for paging in Oracle [duplicate]
  28. How can I get the number of days between 2 dates in Oracle 11g? [duplicate]
  29. How do I call an Oracle stored procedure from an Excel VBA script?
  30. Oracle DB: How can I write query ignoring case?

Leave a Comment