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

Time:10-07

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.

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
  • Related