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.
CodePudding user response:
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