3

I have a table in Redshift like:

category | date
----------------
   1     | 9/29/2016
   1     | 9/28/2016
   2     | 9/28/2016
   2     | 9/28/2016

which I'd like to turn into:

category | 9/29/2016 | 2/28/2016
--------------------------------
   1     |    1      |   1 
   2     |    0      |   2

(count of each category for each date)

Pivot a table with Amazon RedShift / PostgreSQL seems to be helpful using CASE statements but that requires knowing all possible cases beforehand - how could I do this if the columns I want are every day starting from a given date?

jasonm
  • 1,020
  • 2
  • 11
  • 24

2 Answers2

3

There is no functionality provided with Amazon Redshift that can automatically pivot the data.

The Pivot a table with Amazon RedShift / PostgreSQL page you referenced shows how the output can be generated, but it is unable to automatically adjust the number of columns based upon the input data.

One option would be to write a program that queries available date ranges, then generates the SQL query. However, this isn't possible totally within Amazon Redshift.

Community
  • 1
  • 1
John Rotenstein
  • 241,921
  • 22
  • 380
  • 470
0

You could do a self join on date, which i'm currently looking up how to do.

Yale Newman
  • 1,141
  • 1
  • 13
  • 22