How Can I Dynamically Pivot Rows into Columns in Oracle?

Dynamic Pivoting in Oracle: Transforming Rows into Columns

This article addresses the challenge of dynamically pivoting rows into columns in Oracle. This technique allows for transforming data where keys and values are stored in rows, into a tabular format with columns representing the keys and rows representing the values.

The Problem

Given a table with ID as the primary key, keys (K) and values (V), the objective is to create a pivoted table with as many columns as there are unique keys in the original table. The query should be able to handle unknown column names that may arise dynamically.

Oracle 11g Solution

Oracle 11g offers the PIVOT operation that fulfills this requirement. The following query demonstrates its usage:

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

Oracle XML Pivot Option (Oracle 11g)

For scenarios where the column headings are unknown, Oracle provides a pivot XML option, as seen in the query below:

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

Edit:

Modifications were made to the query to aggregate values and include the IN clause, as pointed out in the comments. However, specifying values in the IN clause impedes the query's dynamic nature.

The above is the detailed content of How Can I Dynamically Pivot Rows into Columns in Oracle?. For more information, please follow other related articles on the PHP Chinese website!