4

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

Improve this question
4
  • 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: stackoverflow.com/questions/1128737/unpivot-and-postgresql Commented May 8, 2017 at 6:10
  • I can add your payment group numbers :) Commented May 8, 2017 at 6:36
  • Added whitespace as requested. Commented May 8, 2017 at 6:40
  • Whitespace text version now even better Commented May 8, 2017 at 7:02

2 Answers 2

Reset to default
6

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.

Improve this answer
Sign up to request clarification or add additional context in comments.

4 Comments

thanks for the answer... is it advisable to use this kind of query (unnest), if I want to retrieve 30+ columns?
Just try it out. It should be quite efficient.
This helped me produce a report which would require whole data structure change... thanks for your help.!
Would just like to report that although a bit more hacky, this approach did end up in faster queries when compared to a usual 'UNION' approach. I was thinking about using 'unnest' already before visiting this post, but I wondered if there was a array-less approach. Guess there isn't :)
1

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.

Improve this answer

3 Comments

noted on that... thanks for your answer.. but how can I add the payment group column here, an auto number per payment id
It can be done with the unnest() function though :) There are a lot of magic things the Postgres Elephant can do
Just because it can be done it does not mean it should be.

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.