How to Efficiently Transform Rows into Columns in Oracle?

How to Convert Rows to Columns in Oracle

Facing the challenge of transforming table data with multiple rows and columns into a more readable columnar structure? The Oracle database offers powerful techniques to simplify this task.

Suppose you have a table containing loan information and documents, such as:

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

To convert these rows into structured columns, you can leverage the DECODE function in Oracle 10g or the PIVOT clause introduced in Oracle 11g.

DECODE Function (Oracle 10g)

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 values to the desired columns based on specific document types. The MAX function then returns the matching document ID for each column.

PIVOT Clause (Oracle 11g)

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

The PIVOT clause aligns the document types as columns and places the corresponding document IDs in the appropriate cells.

By utilizing these techniques, you can efficiently reshape your data into a more column-focused layout, making it easier to understand and further analyze.

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