hadoop - hive 列到行

Question

如何根据存储在现有列中的类型在 HIVE 中创建新列？

例如，我有:

id    ProductType
1       car
2       bike
3       truck

我想拥有:

id    car    bike    truck
1      1     null    null
2     null     1     null
3     null   null      1   

Answer 1

Use `case` statement to convert values into columns:

select id, case when ProductType = 'car' then 1 end as car,
           case when ProductType = 'bike' then 1 end as bike,
           case when ProductType = 'truck' then 1 end as truck
 from your_table;

OK
id      car     bike    truck
1       1       NULL    NULL
2       NULL    1       NULL
3       NULL    NULL    1
Time taken: 35.442 seconds, Fetched: 3 row(s)