How to make dummy variables for a same data id in IMPALA SQL

Question

I have a dataset in impala SQL like this:

And I want to look like this:

I have tried using CASE WHEN but results in duplicates for those ids where has 2 values different.

Can someone help me with this issue.

Thenk you much in advance.

Answer 1

select id 
     , MAX(case when var1 = 'AAA' then 1 else 0 end) as var1_AAA 
     , MAX(case when var1 = 'BBB' then 1 else 0 end) as var1_BBB 
     , MAX(case when var1 = 'CCC' then 1 else 0 end) as var1_CCC
from table
group by id