hive column to rows

Question

how can I create new columns in HIVE according to the type stored in an existing column?

for example, I have :

id    ProductType
1       car
2       bike
3       truck

and I would like to have:

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

Duplicate https://stackoverflow.com/questions/23025380/how-to-transpose-pivot-data-in-hive — nobody, Nov 13 '17 at 16:10

score 0 · Answer 1 · answered Nov 15 '17 at 07:49

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)

hive column to rows

1 Answers1