postgresql - Change single row to multiple rows
postgresql, sql
Solution
version with whitespace (empty text) The `unnest` function can do this for you. And if you want the empty text then you can use this
SELECT ROW_NUMBER() OVER (ORDER BY paymentid) AS "group",
unnest(array[1, 2, 3]) AS "line item",
unnest(array[paymentid, '', '']) AS "paymentid",
unnest(array[customercode, '', '']) AS "customercode",
unnest(array['PREVIOUS BALANCE', 'PAYMENT AMOUNT', 'REMAINING BALANCE']) AS "type",
unnest(array[previousbalance, paymentamount, remainingbalance]) AS "amount"
FROM payment_info
ORDER BY 1, 2 ;
To get this
group | line item | paymentid | customercode | type | amount
-------+-----------+-----------+--------------+-------------------+--------
1 | 1 | PID0001 | CUST024 | PREVIOUS BALANCE | 10000
1 | 2 | | | PAYMENT AMOUNT | 2500
1 | 3 | | | REMAINING BALANCE | 7500
2 | 1 | PID0002 | CUST031 | PREVIOUS BALANCE | 8500
2 | 2 | | | PAYMENT AMOUNT | 3500
2 | 3 | | | REMAINING BALANCE | 5000
3 | 1 | PID0003 | CUST005 | PREVIOUS BALANCE | 12000
3 | 2 | | | PAYMENT AMOUNT | 1500
3 | 3 | | | REMAINING BALANCE | 10500
If you want to have, for example points or other text, or arrows in the empty text columns, you can do this easily with `unnest`.
You can control the 4 empty text values individually.
SELECT ROW_NUMBER() OVER (ORDER BY paymentid) AS "group",
unnest(array[1, 2, 3]) AS "line item",
unnest(array[paymentid, ' a', ' c']) AS "paymentid",
unnest(array[customercode, ' b', ' d']) AS "customercode",
unnest(array['PREVIOUS BALANCE', 'PAYMENT AMOUNT', 'REMAINING BALANCE']) AS "type",
unnest(array[previousbalance, paymentamount, remainingbalance]) AS "amount"
FROM payment_info
ORDER BY 1, 2 ;
to generate
group | line item | paymentid | customercode | type | amount
-------+-----------+-----------+--------------+-------------------+--------
1 | 1 | PID0001 | CUST024 | PREVIOUS BALANCE | 10000
1 | 2 | a | b | PAYMENT AMOUNT | 2500
1 | 3 | c | d | REMAINING BALANCE | 7500
2 | 1 | PID0002 | CUST031 | PREVIOUS BALANCE | 8500
2 | 2 | a | b | PAYMENT AMOUNT | 3500
2 | 3 | c | d | REMAINING BALANCE | 5000
3 | 1 | PID0003 | CUST005 | PREVIOUS BALANCE | 12000
3 | 2 | a | b | PAYMENT AMOUNT | 1500
3 | 3 | c | d | REMAINING BALANCE | 10500
It's a very flexible solution, you know.
Problem
I have a table named payment_info, with the following records. ``` paymentid | customercode | previousbalance | paymentamount | remainingbalance ----------------------------------------------------------------------------- PID0001 | CUST024 | 10000 | 2500 | 7500 PID0002 | CUST031 | 8500 | 3500 | 5000 PID0003 | CUST005 | 12000 | 1500 | 10500 ``` Then what I want is to create a 3 rows per row of the above table. I want my results to look like this. ``` Payment Group | Payment Line Item | Payment ID | Customer Code | Type | Amount -------------------------------------------------------------------------------------------------- 1 | 1 | PID0001 | CUST024 | PREVIOUS BALANCE | 10000.00 1 | 2 | | | PAYMENT AMOUNT | 2500.00 1 | 3 | | | REMAINING BALANCE | 7500.00 2 | 1 | PID0002 | CUST031 | PREVIOUS BALANCE | 8500.00 2 | 2 | | | PAYMENT AMOUNT | 3500.00 2 | 3 | | | REMAINING BALANCE | 5000.00 3 | 1 | PID0003 | CUST005 | PREVIOUS BALANCE | 12000.00 3 | 2 | | | PAYMENT AMOUNT | 1500.00 3 | 3 | | | REMAINING BALANCE | 10500.00 ``` Here is the query I've started. But it did not return results same as above. ``` select row_number() over() as id,paymentid,customercode,'PREVIOUS BALANCE' as type,previousbalance from payment_info union select row_number() over() as id,'','','PAYMENT AMOUNT' as type,paymentamount from payment_info union select row_number() over() as id,'','','REMAINING BALANCE' as type,remainingbalance from payment_info ``` Is there other ways, where I will not use UNION Keyword? Cause in the real table, I will be using 30+ columns, querying thousands of records. I also don't know how to create auto generated number (id) from payment group (per payment id) and Payment Line Item (per group). thanks