How to Pivot Rows to Columns in Oracle: DECODE vs. PIVOT?

How to Transform Data from Rows to Columns in Oracle: Pivot Solutions

Oracle databases offer versatile techniques for restructuring data from rows into columns, a process known as pivoting. This transformation becomes necessary when data is organized in a narrow format, and you need to present it in a wider view.

To accomplish row-to-column conversion, Oracle provides two methods:

DECODE Function in Oracle 10g

In earlier versions of Oracle (10g), you can achieve pivoting using the DECODE function. Consider the following table structure:

LOAN NUMBER	DOCUMENT_TYPE	DOCUMENT_ID
992452533663	Voters ID	XPD0355636
992452533663	Pan card	CHXPS5522D
992452533663	Drivers licence	DL-0420110141769

To pivot this data, you can utilize the DECODE function as follows:

SELECT
    loan_number,
    MAX(DECODE(document_type, 'Voters ID', document_id)) AS voters_id,
    MAX(DECODE(document_type, 'Pan card', document_id)) AS pan_card,
    MAX(DECODE(document_type, 'Drivers licence', document_id)) AS drivers_licence
  FROM
    doc_tab
GROUP BY loan_number
ORDER BY loan_number;

This query groups the data by loan number and uses nested DECODE functions to assign document IDs to specific document types. The MAX function returns the desired single value for each loan-document combination.

PIVOT Clause in Oracle 11g and Later

Oracle 11g introduced a dedicated PIVOT clause to simplify row-to-column transformations. The following query demonstrates its usage:

SELECT *
  FROM doc_tab
PIVOT (
  MAX(document_id) FOR document_type IN ('Voters ID','Pan card','Drivers licence')
);

The PIVOT clause in this query explicitly defines the columns to be created in the transformed result. It automatically groups the data based on the specified document types and combines the matching document IDs.

For further insight into pivoting techniques in Oracle, refer to the resources below:

• [Tim Hall's article on Pivoting in Oracle](https://www.oracle.com/technetwork/database/oracle-magazine/oracle-11g-pivot-127859.html)
• [SQLFiddle example comparing both solutions](https://www.sqlfiddle.com/#!5/7956/2/0)

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