Oracle SQL - Unpivot kind of logic but without hard coding

Question

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

Does this answer your question? [Oracle 11g: Unpivot multiple columns and include column name](https://stackoverflow.com/questions/10747355/oracle-11g-unpivot-multiple-columns-and-include-column-name) — astentx, Jul 22 '22 at 10:09

score 1 · Accepted Answer · answered Jul 22 '22 at 09:29

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

Oracle SQL - Unpivot kind of logic but without hard coding

1 Answers1