How to Convert Rows to Columns in Oracle Using DECODE and PIVOT?

Convert Rows to Columns in Oracle: Leveraging DECODE and PIVOT

Problem:

Transform a table with rows representing multiple document types and IDs associated with a loan number into a columnar format. The resulting table should have a single row for each loan number, with columns for each document type and the corresponding ID.

Example:

Input Table:

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

Desired Output:

LOAN NUMBER	VOTERS_ID	PAN_CARD	DRIVERS LICENCE
992452533663	XPD0355636	CHXPS5522D	DL-0420110141769

Oracle Solutions:

DECODE Method:

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
    input_table
GROUP BY loan_number
ORDER BY loan_number;

PIVOT Clause (Oracle 11g ):

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

Both methods effectively transpose the rows into columns, achieving the desired outcome. Explore the referenced links for additional details on Oracle DECODE and PIVOT.

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