0

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.

Craig Ringer
  • 307,061
  • 76
  • 688
  • 778
Blip
  • 3,061
  • 5
  • 22
  • 50

1 Answers1

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.

klin
  • 112,967
  • 15
  • 204
  • 232
  • but how do I select for a specific `date`? do I use `WHERE "date" = '2016-11-25'` after `from cash_drawer c`? – Blip Nov 25 '16 at 14:35
  • Of course, you can freely filter the data in `where` clause. See the edited answer. – klin Nov 25 '16 at 14:50