1

I have a scenario where for a purchase i can be paid with either cash,card or voucher are all the above three. But i need a report to show the all the payment types in seperate lines but i captured all these value in a single row. the problem is how to transfer the rows values into columns .

For Ex.

╔═════════════════════════════════════════════════════╗
║ Order           Amount     Cash     Card    Voucher ║
╠═════════════════════════════════════════════════════╣
║ Purchase1       1000       500      300     200     ║
╚═════════════════════════════════════════════════════╝

but i need the result has

╔══════════════════════════════════╗
║ Order           Amount  PayType  ║
╠══════════════════════════════════╣
║ Purchase1        500     Cash    ║
║ Purchase1        300     Card    ║
║ Purchase1        200     Voucher ║
╚══════════════════════════════════╝

Let can provide your suggestions to accomplish the above format.

Barbaros Özhan
  • 59,113
  • 10
  • 31
  • 55
Java Learner
  • 87
  • 1
  • 2
  • 8
  • Can you use php, java, or another language to edit the result set? – MrMadsen Jul 15 '15 at 04:25
  • I need to do this through postgres sql query. – Java Learner Jul 15 '15 at 04:27
  • originally you have the data in wide format, but you want the data in narrow format. This is a well known problem and many languages such as R have implemented methods to do what you want. see [wikipedia](https://en.wikipedia.org/wiki/Wide_and_narrow_data). However I'm not sure whether SQL has methods to do this or if you need to loop through the cash card and voucher columns and use an insert statement. – nathanesau Jul 15 '15 at 04:30
  • 1
    This is generally called an unpivot. – Craig Ringer Jul 15 '15 at 04:41

3 Answers3

1

One way to unpivot your table

SELECT "Order", 
       CASE WHEN n = 1 THEN Cash
            WHEN n = 2 THEN Card 
            WHEN n = 3 THEN Voucher END Amount,
       CASE WHEN n = 1 THEN 'Cash' 
            WHEN n = 2 THEN 'Card'
            WHEN n = 3 THEN 'Voucher' END PayType      
  FROM table1 CROSS JOIN generate_series(1, 3) n

Output:

Order amount paytype
Purchase1 500 Cash
Purchase1 300 Card
Purchase1 200 Voucher

Here is a SQLFiddle demo

Barbaros Özhan
  • 59,113
  • 10
  • 31
  • 55
peterm
  • 91,357
  • 15
  • 148
  • 157
1

Using unnest in PostgreSQL Array Functions 

SELECT "Order",
        unnest(array[cash, card, voucher]) AS "amount",
        unnest(array['cash', 'card', 'voucher']) AS "PayType"
FROM bar
ORDER BY "PayType"

sqlfiddle

Vivek S.
  • 19,945
  • 7
  • 68
  • 85
0

I suggest you to use UNION ALL like this:

SELECT Order, Cash As Amount, 'Cash' As PayType
UNION ALL
SELECT Order, Card, 'Card'
UNION ALL
SELECT Order, Voucher, 'Voucher'
shA.t
  • 16,580
  • 5
  • 54
  • 111