How to Transform Rows into Columns in Oracle Using DECODE and PIVOT?

How to Convert Rows to Columns in Oracle

In Oracle, you can face scenarios where data is arranged in rows but it needs to be displayed in a columnar fashion. This article delves into the methods for converting rows into columns using the DECODE function or the PIVOT clause.

Consider the following table containing loan details:

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

To pivot the rows into columns, you can use 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 license', document_id)) AS drivers_licence
  FROM
    doc_tab
GROUP BY loan_number
ORDER BY loan_number;

This query will produce the following output:

LOAN_NUMBER   VOTERS_ID            PAN_CARD             DRIVERS_LICENCE    
------------- -------------------- -------------------- --------------------
992452533663  XPD0355636           CHXPS5522D           DL-0420110141769     

In Oracle versions 11g and above, the PIVOT clause provides a simpler way to achieve the same result:

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

Both methods can effectively pivot rows into columns, allowing you to manipulate and display your data in the preferred format.

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