How to Pivot table on Oracle SQL

November 22, 2023

I would like to pivot the columns in the original table (Table1) below to produce the desired output on Oracle SQL. Below is the query to create the table.

CREATE TABLE ReconciliationTable (
RECONCILIATION_DATE DATE,
Account_Name VARCHAR(50),
Currency VARCHAR(3),
Offshore_Account VARCHAR(50),
Offshore_Amount DECIMAL(18, 2),
Offshore_Difference DECIMAL(18, 2),
Ledger_Account VARCHAR(50),
Ledger_Amount DECIMAL(18, 2),
Ledger_Difference DECIMAL(18, 2),
Offshore_Ledger_Difference DECIMAL(18, 2)
);

INSERT INTO ReconciliationTable VALUES 
('2023-10-31', 'Bank1', 'USD', 'Opening Balance', 16019912.00, NULL, 'Opening Balance', 0.00, NULL, 16019912.00),
('2023-10-31', 'Bank1', 'USD', 'Closing Balance', 16019912.00, 0.00, 'Closing Balance', 0.00, 0.00, 16019912.00),
('2023-10-31', 'Bank2', 'CHF', 'Opening Balance', 872864.00, NULL, 'Opening Balance', 0.00, NULL, 872864.00),
('2023-10-31', 'Bank2', 'CHF', 'Closing Balance', 873725.00, 861.00, 'Closing Balance', 0.00, 0.00, 873725.00),
('2023-10-31', 'Bank3', 'CAD', 'Opening Balance', 10831173.00, NULL, 'Opening Balance', 233782136.00, NULL, -222950963.00),
('2023-10-31', 'Bank3', 'CAD', 'Closing Balance', 10842614.00, 11441.00, 'Closing Balance', 233782136.00, 0.00, -222939522.00),
('2023-11-02', 'Bank1', 'USD', 'Opening Balance', 80693853.00, NULL, 'Opening Balance', 3508963518.00, NULL, -3428269665.00),
('2023-11-02', 'Bank1', 'USD', 'Closing Balance', 80772301.00, 78448.00, 'Closing Balance', 3508963518.00, 0.00, -3428191217.00),
('2023-11-02', 'Bank2', 'CHF', 'Opening Balance', 58156966.00, NULL, 'Opening Balance', 149734549.00, NULL, -91577583.00),
('2023-11-02', 'Bank2', 'CHF', 'Closing Balance', 58156966.00, 0.00, 'Closing Balance', 49734549.00, NULL, 0.00, -91577583.00),
('2023-11-02', 'Bank3', 'CAD', 'Opening Balance', 18380423.00, NULL, 'Opening Balance', 489910.00, NULL, 17890513.00),
('2023-11-02', 'Bank3', 'CAD', 'Closing Balance', 18380423.00, 0.00, 'Closing Balance', 489910.00, 0.00, 17890513.00);

The 2 values in ‘Offshore Account’ become column names Offshore Opening Balance and Offshore Closing Balance

The 2 values in ‘Ledger Account’ become column names Ledger Opening Balance and Ledger Closing Balance

Please also note that:

Offshore Difference = Offshore Closing Balance – Offshore Opening Balance

Ledger Difference = Ledger Closing Balance – Ledger Opening Balance

Offshore/Ledger Difference (Opening Balance) = Offshore Opening Balance – Ledger Opening Balance

Offshore/Ledger Difference (Closing Balance) = Offshore Closing Balance – Ledger Closing Balance

Table1

RECONCILIATION_DATE	Account Name	Currency	Offshore Account	Offshore Amount	Offshore Difference	Ledger Account	Ledger Amount	Ledger Difference	Offshore/Ledger Difference
31-Oct-23	Bank1	USD	Opening Balance	16,019,912.00		Opening Balance	0.00		16,019,912.00
31-Oct-23	Bank1	USD	Closing Balance	16,019,912.00	0.00	Closing Balance	0.00	0.00	16,019,912.00
31-Oct-23	Bank2	CHF	Opening Balance	872,864.00		Opening Balance	0.00		872,864.00
31-Oct-23	Bank2	CHF	Closing Balance	873,725.00	861.00	Closing Balance	0.00	0.00	873,725.00
31-Oct-23	Bank3	CAD	Opening Balance	10,831,173.00		Opening Balance	233,782,136.00		-222,950,963.00
31-Oct-23	Bank3	CAD	Closing Balance	10,842,614.00	11,441.00	Closing Balance	233,782,136.00	0.00	-222,939,522.00
02-Nov-23	Bank1	USD	Opening Balance	80,693,853.00		Opening Balance	3,508,963,518.00		-3,428,269,665.00
02-Nov-23	Bank1	USD	Closing Balance	80,772,301.00	78,448.00	Closing Balance	3,508,963,518.00	0.00	-3,428,191,217.00
02-Nov-23	Bank2	CHF	Opening Balance	58,156,966.00		Opening Balance	149,734,549.00		-91,577,583.00
02-Nov-23	Bank2	CHF	Closing Balance	58,156,966.00	0.00	Closing Balance	49,734,549.00		0.00
02-Nov-23	Bank3	CAD	Opening Balance	18,380,423.00		Opening Balance	489,910.00		17,890,513.00
02-Nov-23	Bank3	CAD	Closing Balance	18,380,423.00	0.00	Closing Balance	489,910.00	0.00	17,890,513.00

Desired Output:

RECONCILIATION_DATE	Account Name	Currency	Offshore Opening Balance	Offshore Closing Balance	Offshore Difference	Ledger Opening Balance	Ledger Closing Balance	Offshore/Ledger Difference (Opening Balance)	Offshore/Ledger Difference (Closing Balance)
31-Oct-23	Bank1	USD	16,019,912.00	16,019,912.00	0.00	0.00	0.00	16,019,912.00	16,019,912.00
31-Oct-23	Bank2	CHF	872,864.00	873,725.00	861.00	0.00	0.00	872,864.00	873,725.00
31-Oct-23	Bank3	CAD	10,831,173.00	10,842,614.00	11,441.00	233,782,136.00	233,782,136.00	-222,950,963.00	-222,939,522.00
02-Nov-23	Bank1	USD	80,693,853.00	80,772,301.00	78,448.00	3,508,963,518.00	3,508,963,518.00	-3,428,269,665.00	-3,428,191,217.00
02-Nov-23	Bank2	CHF	58,156,966.00	58,156,966.00	0.00	149,734,549.00	149,734,549.00	-91,577,583.00	-91,577,583.00
02-Nov-23	Bank3	CAD	18,380,423.00	18,380,423.00	0.00	489,910.00	489,910.00	17,890,513.00	17,890,513.00

>Solution :

To achieve the desired output, you can use the Oracle SQL CASE statement to pivot the columns and calculate the differences. Here’s a query that should help you achieve this:

SELECT
    RECONCILIATION_DATE,
    "Account Name",
    Currency,
    MAX(CASE WHEN "Offshore Account" = 'Opening Balance' THEN "Offshore Amount" END) AS "Offshore Opening Balance",
    MAX(CASE WHEN "Offshore Account" = 'Closing Balance' THEN "Offshore Amount" END) AS "Offshore Closing Balance",
    MAX(CASE WHEN "Offshore Account" = 'Closing Balance' THEN "Offshore Amount" END) -
    MAX(CASE WHEN "Offshore Account" = 'Opening Balance' THEN "Offshore Amount" END) AS "Offshore Difference",
    MAX(CASE WHEN "Ledger Account" = 'Opening Balance' THEN "Ledger Amount" END) AS "Ledger Opening Balance",
    MAX(CASE WHEN "Ledger Account" = 'Closing Balance' THEN "Ledger Amount" END) AS "Ledger Closing Balance",
    MAX(CASE WHEN "Ledger Account" = 'Closing Balance' THEN "Ledger Amount" END) -
    MAX(CASE WHEN "Ledger Account" = 'Opening Balance' THEN "Ledger Amount" END) AS "Ledger Difference",
    MAX(CASE WHEN "Offshore Account" = 'Opening Balance' THEN "Offshore Amount" END) -
    MAX(CASE WHEN "Ledger Account" = 'Opening Balance' THEN "Ledger Amount" END) AS "Offshore/Ledger Difference (Opening Balance)",
    MAX(CASE WHEN "Offshore Account" = 'Closing Balance' THEN "Offshore Amount" END) -
    MAX(CASE WHEN "Ledger Account" = 'Closing Balance' THEN "Ledger Amount" END) AS "Offshore/Ledger Difference (Closing Balance)"
FROM
    Table1
GROUP BY
    RECONCILIATION_DATE, "Account Name", Currency;

This query uses the CASE statement to conditionally aggregate the values based on the conditions you provided. The MAX function is used to handle the aggregation, and the GROUP BY clause is used to group the results by the specified columns.

Please note that the column names in the query are enclosed in double quotes (") to handle case sensitivity and spaces in column names. If your column names are case-insensitive or don’t contain spaces, you can omit the double quotes.

Feel free to adjust the query based on your specific database setup and column naming conventions.

Another way to achieve the desired output is by using the PIVOT clause. Here’s a simplified query using PIVOT:

SELECT
    RECONCILIATION_DATE,
    "Account Name",
    Currency,
    "Opening Balance" AS "Offshore Opening Balance",
    "Closing Balance" AS "Offshore Closing Balance",
    "Closing Balance" - "Opening Balance" AS "Offshore Difference",
    "Ledger Opening Balance",
    "Ledger Closing Balance",
    "Ledger Closing Balance" - "Ledger Opening Balance" AS "Ledger Difference",
    "Offshore Opening Balance" - "Ledger Opening Balance" AS "Offshore/Ledger Difference (Opening Balance)",
    "Offshore Closing Balance" - "Ledger Closing Balance" AS "Offshore/Ledger Difference (Closing Balance)"
FROM (
    SELECT
        RECONCILIATION_DATE,
        "Account Name",
        Currency,
        "Offshore Account",
        "Offshore Amount",
        "Ledger Account",
        "Ledger Amount"
    FROM Table1
)
PIVOT (
    MAX("Offshore Amount") AS Offshore,
    MAX("Ledger Amount") AS Ledger
    FOR ("Offshore Account", "Ledger Account") IN (
        ('Opening Balance' AS "Opening Balance", 'Closing Balance' AS "Closing Balance")
    )
);

In this query:

The inner query selects the relevant columns from your original table.
The PIVOT clause then pivots the data based on the specified columns (‘Offshore Account’ and ‘Ledger Account’) and their values (‘Opening Balance’ and ‘Closing Balance’).
Finally, the outer query performs the necessary calculations to get the desired output.

This approach simplifies the syntax by directly using the PIVOT clause to transform the data. Adjust the column names as needed based on your actual database setup.