Transpose Dataset In Hive

I'm trying to transpose a variable in Hive such as: Id1 Id2 Event 1 1 7 2 2 3 2 2 7 to Id1 Id2 Event_7 Event_3 1 1 1 2 2 1 1 Following

Solution 1:

In Hive SQL, you can do conditional aggregation:

select 
    id1,
    id2,
    max(case when event = 7 then 1 end) event_7,
    max(case when event = 3 then 1 end) event_3
group by id1, id2
order by id1, id2