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:

Desired Output:

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!