user2475110 user2475110 - 1 year ago 103
SQL Question

Pivot Table with Redshift (PosgreSQL) with Count

I'm facing a challenge with Redshift:
I'm trying to dynamically move rows into columns and aggregate by count, however I noticed the pivot table feature is only available from PostgreSQL 9.

Any idea about how to do the following?

index fruit color
1 apple red
2 apple yellow
2 banana blue
2 banana blue
3 banana blue
3 banana green
3 pear green
3 pear red


to:

index red yellow blue green
1 1 0 0 0
2 0 1 2 0
3 1 0 1 2


Essentially, grouping and counting occurrences of color per id (fruit is not so important, although I'll use it as a filter later).

Note: I might also want to do a binary transformation later on (i.e 0 for 0 and 1 if > 0)

Edit: If the above is not possible, any way to do this instead ?

index color count
1 red 1
1 yellow 0
1 blue 0
1 green 0
2 red 0
2 yellow 1
2 blue 2
2 green 0
3 red 1
3 yellow 0
3 blue 1
3 green 2


(again blue,yellow,blue and green should be dynamic)

Answer Source

For the Edit, you could do

select x.index, x.color, sum(case when y.index is not null then 1 else 0 end) as count
from 
((select index
from [table]
group by index
order by index) a
inner join 
(select color
from [table]
group by color
order by color) b
on 1 = 1) x
left outer join
[table] y
on x.index = y.index
and x.color = y.color
group by x.index, x.color
order by x.index, x.color
Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download