Search code examples
sqloracleoracle12cunpivot

Oracle SQL - Unpivot kind of logic but without hard coding

I have table like below , there are many number of lines with debit/Credit string and respective amounts in a table , need to bring as expected table like in separate lines of each strings and its respective amounts , how can i achieve this, appreciate your help!

Debit String Credit String Debit Amount Credit Amount
ING1 ING2 123 0
INT2 INT5 234 0

Expected Result is :-

String Amount
ING1 123
INT2 234
ING2 0
INT5 0
Solution

  • Use UNPIVOT:

    SELECT *
FROM   table_name
UNPIVOT (
  (string, amount) FOR type IN (
    (debit_string,  debit_amount ) AS 'D',
    (credit_string, credit_amount) AS 'C'
  )
);

    Which, for the sample data:

    CREATE TABLE table_name (Debit_String, Credit_String, Debit_Amount, Credit_Amount) AS
SELECT 'ING1', 'ING2', 123, 0 FROM DUAL UNION ALL
SELECT 'INT2', 'INT5', 234, 0 FROM DUAL;

    Outputs:

    TYPE STRING AMOUNT
    D ING1 123
    C ING2 0
    D INT2 234
    C INT5 0

    db<>fiddle here