Presto – pivot table

Hi I have a table like this:

Session id  Property
1           Radar
2           Bullet
1           Bullet
3           Radar
2           Price

I want to convert it into like this:

Radar   Bullet   Price
  1       1
          2        2
  3

Answer

For a fixed list of properties, you can do conditional aggregation:

select session_id,
    max(case when property = 'Radar'  then 1 else 0 end) as radar,
    max(case when property = 'Bullet' then 1 else 0 end) as bullet,
    max(case when property = 'Price'  then 1 else 0 end) as price
from mytable
group by session_id

This puts the session id in the first column and 0/1 values in each column, depending on whether the given session owns the given property.

To generate the exact output you showed (which might be a little less helpful than the above), you would do:

select 
    case when max(case when property = 'Radar'  then 1 else 0 end) = 1 then session_id end as radar,
    case when max(case when property = 'Bullet' then 1 else 0 end) = 1 then session_id end as bullet,
    case when max(case when property = 'Price'  then 1 else 0 end) = 1 then session_id end as price 
from mytable
group by session_id