sqloraclepivot

How to Pivot table on Oracle SQL


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 Ledger Difference 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 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 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 0.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 0.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 0.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 0.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.