SQL query to make one column for every date in range

Asked Jan 28 '16 at 17:48

Active Jan 29 '16 at 11:40

Viewed 92 times

Give a simple table like this:

ClientCode | Date       | Amount
--------------------------------
1          | 2014-01-01 | 10
1          | 2014-01-01 | 20
2          | 2014-01-01 | 30
1          | 2014-01-02 | 15

I need a query that would list the count of rows for every client for every day like this:

ClientCode | day2014-01-01 | day2014-01-02 | day2014-01-03
1          |             2 |             1 |             0
2          |             1 |             0 |             0

I could make a CASE for every day but I need it for 120 days and about 80000 clients.

Any advice?

edited Jan 28 '16 at 18:53

asked Jan 28 '16 at 17:48

Facundo Casco

10,065
8
42
63

Search for `crosstab` in the [`tablefunc` module](http://www.postgresql.org/docs/current/interactive/tablefunc.html). – vyegorov Jan 28 '16 at 18:15
Likely a duplicate of [this question](http://stackoverflow.com/q/3002499/519015) – Aryeh Leib Taurog Feb 02 '16 at 11:58

SQL query to make one column for every date in range

0 Answers0