0

Say I have a table with the following data, and I want to group by customer_id. 

| 'customer_id' | 'date_of_purchase' |
|---------------|--------------------|
| AAA           | '2019-01-01'       |
| AAA           | '2019-02-01'       |
| AAA           | '2019-03-01'       |
| BBB           | '2019-01-20'       |
| BBB           | '2019-02-20'       |
| CCC           | '2019-01-01'       |
| CCC           | '2019-03-10'       |
| CCC           | '2019-05-10'       |
| CCC           | '2019-08-10'       |

My endgoal is to have a table where each customer_id gets a row, with variable number of columns. Something that looks like this

| customer_id | some header  | some header  | some header  | some header  |
|-------------|--------------|--------------|--------------|--------------|
| AAA         | '2019-01-01' | '2019-02-01' | '2019-03-01' |              |
| BBB         | '2019-01-20' | '2019-02-20' |              |              |
| CCC         | '2019-01-01' | '2019-03-10' | '2019-05-10' | '2019-08-10' |

I'm not sure if crosstab() can accomplish this task. Is this even possible in postgresql?

Thanks in advance for the help

Clara
  • 106
  • 5
  • Check out these two answers from Erwin. https://stackoverflow.com/questions/20618323/create-a-pivot-table-with-postgresql https://stackoverflow.com/questions/3002499/postgresql-crosstab-query/11751905#11751905 – J Spratt Aug 19 '19 at 23:18
  • So the number of columns is not fixed, right? – S-Man Aug 20 '19 at 06:44
  • @S-Man That's correct – Clara Aug 20 '19 at 17:17
  • Then it is nearly impossible. A query needs to return always the same numbed of columns. One workaround could be to set a maximum number of columns, lets say 5 for your example. In that case the last one would be left empty. Other way could be an array of dates instead of real columns. In this case you could enter the date by the array index. – S-Man Aug 20 '19 at 17:19
  • @JSpratt Thanks for the hint. The problem for me is that I don't know a priori what the column headers will be, so it's not a simple aggregate of "active" or "inactive". I want the non-aggregated values for each group to be unstacked horizontally, with an indefinite number of columns. So in the above example, potentially if customer_id "DDD" had made 100 purchases on 100 different days, then the resulting table needs to be expanded to be 101 columns to accomendate that. – Clara Aug 20 '19 at 17:20
  • @S-Man Ok, thank you very much. I wanted to investigate whether this is possible. If it's not, I'll have to figure out a work around. – Clara Aug 20 '19 at 17:21

0 Answers0