postgresql - Change single row to multiple rows

Question

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

Use **UNION ALL** which brings back all rows at less cost than UNION by itself. also: There is no guarantee that **row_number() over()** will produce the required sorting - use and EXPLICIT order by to guarantee the corret sorting. also see: http://stackoverflow.com/questions/1128737/unpivot-and-postgresql — Paul Maxwell, May 08 '17 at 06:10

flutter · Accepted Answer · 2017-05-08T07:01:53.053

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.

thanks for the answer... is it advisable to use this kind of query (unnest), if I want to retrieve 30+ columns? — , May 08 '17 at 11:33
This helped me produce a report which would require whole data structure change... thanks for your help.! — emarshah, Jun 07 '20 at 04:30

score 0 · Answer 2 · answered May 08 '17 at 06:20

It isn't necessary to always use union queries. Here for example you can use 3 rows and a cross join instead. This has the advantage of only a single pass over the source table.

drop table if exists Table1;

CREATE TABLE Table1
    ("paymentid" varchar(7), "customercode" varchar(7)
     , "previousbalance" int, "paymentamount" int, "remainingbalance" int)
;

INSERT INTO Table1
    ("paymentid", "customercode", "previousbalance", "paymentamount", "remainingbalance")
VALUES
    ('PID0001', 'CUST024', 10000, 2500, 7500),
    ('PID0002', 'CUST031', 8500, 3500, 5000),
    ('PID0003', 'CUST005', 12000, 1500, 10500)
;

select
      paymentid
    , customercode
    , rn
    , typeof
    , case when rn = 1 then previousbalance
           when rn = 2 then paymentamount
           when rn = 3 then remainingbalance
      end as Amount
from Table1
cross join (select 1 rn , 'previousbalance' typeof
            union all 
            select 2 , 'paymentamount'
            union all 
            select 3, 'remainingbalance'
           ) rns

That data/query produces this result:

+----+-----------+--------------+----+------------------+--------+
|    | paymentid | customercode | rn |      typeof      | amount |
+----+-----------+--------------+----+------------------+--------+
|  1 | PID0001   | CUST024      |  1 | previousbalance  |  10000 |
|  2 | PID0001   | CUST024      |  2 | paymentamount    |   2500 |
|  3 | PID0001   | CUST024      |  3 | remainingbalance |   7500 |
|  4 | PID0002   | CUST031      |  1 | previousbalance  |   8500 |
|  5 | PID0002   | CUST031      |  2 | paymentamount    |   3500 |
|  6 | PID0002   | CUST031      |  3 | remainingbalance |   5000 |
|  7 | PID0003   | CUST005      |  1 | previousbalance  |  12000 |
|  8 | PID0003   | CUST005      |  2 | paymentamount    |   1500 |
|  9 | PID0003   | CUST005      |  3 | remainingbalance |  10500 |
+----+-----------+--------------+----+------------------+--------+

Please then note that SQL isn't a "report writer" so blanks in columns for "layout" are not a good fit for SQL which wants to repeat information (like you see above in the result) so that you can sort and filter as needed.

noted on that... thanks for your answer.. but how can I add the payment group column here, an auto number per payment id — , May 08 '17 at 06:25
It can be done with the unnest() function though :) There are a lot of magic things the Postgres Elephant can do — flutter, May 08 '17 at 06:43

postgresql - Change single row to multiple rows

2 Answers2