Skip to content
Advertisement

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

I have a dataset in impala SQL like this:

enter image description here

And I want to look like this:

enter image description here

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
User contributions licensed under: CC BY-SA
1 People found this is helpful
Advertisement