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.
Advertisement
Answer
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