How to Efficiently Pivot Rows into Columns in Oracle Using DECODE or PIVOT?

Converting Rows to Columns in Oracle

Data tables often contain information spread across multiple rows for individual entities. To improve data readability and analysis, it can be beneficial to convert these rows into columns. This question demonstrates a practical use case of converting a table with document information for a single loan number into a more user-friendly format.

In Oracle, rows can be efficiently pivoted into columns using the DECODE function or the PIVOT clause. Let's explore how to use these methods to solve the problem presented in the question:

Using the DECODE Function

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 uses the DECODE function to assign the document_id value to the appropriate column based on the document_type. The MAX() aggregate function is used to retrieve the most recent document_id for each document type.

Using the PIVOT Clause (available in Oracle 11g )

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

The PIVOT clause provides a more concise way to pivot the rows into columns. It requires specifying the aggregation function (MAX in this case) and the columns to be pivoted.

Both approaches effectively convert the rows into columns, providing a more intuitive and organized view of the document information for each loan number.

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