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.

Answer 1

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.