SQL rows to Column values

Question

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.

Can you use php, java, or another language to edit the result set? — MrMadsen
– MrMadsen, Commented Jul 15, 2015 at 4:25
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. 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
– nathanesau, Commented Jul 15, 2015 at 4:30

Vivek S. · Accepted Answer · 2015-07-15 06:21:55Z

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

edited Jul 15, 2015 at 6:21

answered Jul 15, 2015 at 6:14

Vivek S.

22.3k9 gold badges71 silver badges87 bronze badges

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

Comments

Barbaros Özhan · Accepted Answer · 2021-05-23 12:12:45Z

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

edited May 23, 2021 at 12:12

Barbaros Özhan

65.9k11 gold badges36 silver badges64 bronze badges

answered Jul 15, 2015 at 4:42

peterm

93k16 gold badges157 silver badges162 bronze badges

Comments

shA.t · Accepted Answer · 2015-07-15 04:32:39Z

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'

answered Jul 15, 2015 at 4:32

shA.t

17.1k5 gold badges59 silver badges121 bronze badges

1 Comment

Java Learner Over a year ago

I knew it, but there will be any performance issues. can i get the result apart from union all.

Collectives™ on Stack Overflow

SQL rows to Column values

3 Answers 3

Comments

Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

Comments

Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related