Pivot for redshift database

Question

I know this question has been asked before but any of the answers were not able to help me to meet my desired requirements. So asking the question in new thread

In redshift how can use pivot the data into a form of one row per each unique dimension set, e.g.:

id         Name               Category         count
8660     Iced Chocolate         Coffees         105
8660     Iced Chocolate         Milkshakes      10
8662     Old Monk               Beer            29
8663     Burger                 Snacks          18

to

id        Name              Cofees  Milkshakes  Beer  Snacks
8660    Iced Chocolate       105       10        0      0
8662    Old Monk             0         0        29      0
8663    Burger               0         0         0      18

The category listed above gets keep on changing. Redshift does not support the pivot operator and a case expression would not be of much help (if not please suggest how to do it)

How can I achieve this result in redshift?

(The above is just an example, we would have 1000+ categories and these categories keep's on changing)

Answer 1

i don't think there is a easy way to do that in Redshift,

also you say you have more then 1000 categories and the number is growing you need to taking in to account you have limit of 1600 columns per table,

see attached link [http://docs.aws.amazon.com/redshift/latest/dg/r_CREATE_TABLE_usage.html][1]

you can use case but then you need to create case for each category

select id,
       name,
       sum(case when Category='Coffees' then count end) as Cofees,       
       sum(case when Category='Milkshakes' then count end) as Milkshakes,
       sum(case when Category='Beer' then count end) as Beer,
       sum(case when Category='Snacks' then count end) as Snacks
from my_table
group by 1,2

other option you have is to upload the table for example to R and then you can use cast function for example.

cast(data, name~ category)

and then upload the data back to S3 or Redshift