Convert a single row into multiple rows by the columns in Postgresql

Question

I have a table cash_drawer which stores quantity for each denomination of currency for each day at day end:

cash_drawer(
    date DATE,
    100 SMALLINT,
    50 SMALLINT,
    20 SMALLINT,
    10 SMALLINT,
    5 SMALLINT,
    1 SMALLINT
)

Now any given day, I wish to get each denomination as a row.

If lets say for day 2016-11-25, if we have the following row:

+------------+-------+------+------+------+-----+-----+
| date       |  100  |  50  |  20  |  10  |  5  |  1  |
+------------+-------+------+------+------+-----+-----+
| 2016-11-25 |   5   |  12  |  27  |  43  | 147 | 129 |
+------------+-------+------+------+------+-----+-----+

Now I wish to get the out put of the query as:

+------------+--------+
|denomination|quantity|
+------------+--------+
|100         |5       |
+------------+--------+
|50          |12      |
+------------+--------+
|20          |27      |
+------------+--------+
|10          |43      |
+------------+--------+
|5           |147     |
+------------+--------+
|1           |129     |
+------------+--------+

Is there a method by which this is possible? If you have any other suggestion please be free to suggest.

check this one. stackoverflow.com/a/6360077/3470178

Juan Carlos Oropeza
– Juan Carlos Oropeza

2016-11-25 14:23:53 +00:00
Commented Nov 25, 2016 at 14:23 — Juan Carlos Oropeza
– Juan Carlos Oropeza, Commented Nov 25, 2016 at 14:23

klin · Accepted Answer · 2016-11-25 14:46:44Z

2

Use json functions:

select key as denomination, value as quantity
from cash_drawer c,
lateral json_each(row_to_json(c))
where key <> 'date'
and date = '2016-11-25';

 denomination | quantity 
--------------+----------
 100          | 5
 50           | 12
 20           | 27
 10           | 43
 5            | 147
 1            | 129
(6 rows)

Test it here.

edited Nov 25, 2016 at 14:46

answered Nov 25, 2016 at 14:28

klin

123k15 gold badges240 silver badges262 bronze badges

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

2 Comments

Blip Over a year ago

but how do I select for a specific date? do I use WHERE "date" = '2016-11-25' after from cash_drawer c?

klin Over a year ago

Of course, you can freely filter the data in where clause. See the edited answer.

Collectives™ on Stack Overflow

Convert a single row into multiple rows by the columns in Postgresql

1 Answer 1

2 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related