How to Dynamically Pivot Rows into Columns in Oracle?

Pivoting Rows into Columns Dynamically in Oracle

You have a table with pairs of keys and values in Oracle 10g and would like to transform it into a table with columns for each unique key, with the corresponding value in each row. The resulting table should dynamically adjust to any new key-value pairs added to the original table.

Oracle 11g Solution

Oracle 11g introduces the PIVOT operation, which enables you to perform such dynamic pivoting:

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

This query will create a table with columns for each of the four keys specified in the IN clause.

XML Pivoting Option

Oracle 11g also provides a pivot XML option that allows you to handle unknown column headings:

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

This query will produce an XML result set, where each column corresponds to a unique key, and the associated value is stored as the XML element's value.

Notes:

• The provided solutions assume that the values for each key are aggregated (max is used), but you can adjust the aggregate function as needed.
• It's important to note that while the pivot XML option allows for any number of unknown columns, it requires specifying the columns in the IN clause, which may not always be feasible.

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