CODEWITHSUNDEEP
sqlhive
minnu
minnu

Reputation: 57

Based on the value of a column how to populate various other columns in Hive

In my below example table I have a column id and hobbies

   id  | hobbies   |
--------------------
0001   | reading   |
0001   | playing   |
0001   | swimming  |
0001   | badminton |

I need the output as boolean values based on the hobbies and populate it to various columns in a single row

 id  | reading | playing | swimming | badminton | stamp collection | 
--------------------------------------------------------------------
0001 |  1      | 1       | 1        |1          |0

How to transpose the table?

Upvotes: 0

Views: 44

Answers (2)

Yogesh Sharma
Yogesh Sharma

Reputation: 50163

You wan conditional aggregation :

select id, max(case when hobbies = 'reading' then 1 else 0 end) as reading, 
           max(case when hobbies = 'playing' then 1 else 0 end) as playing,
           max(case when hobbies = 'swimming' then 1 else 0 end) as swimming,
           max(case when hobbies = 'badminton' then 1 else 0 end) as badminton,
           max(case when hobbies = 'stamp collection' then 1 else 0 end) as 'stamp collection'
from table t
group by id;

Upvotes: 1

Fahmi
Fahmi

Reputation: 37473

Try with case when:

select id,case when hobbies='reading' then 1 end as reading,
case when hobbies='playing' then 1 end as playing,case when hobbies='swimming' then 1 end as swimming, case when hobbies='badminton' then 1 end as 'badminton',
case when hobbies='stamp collection' then 0 end as 'stamp collection'
from tablename

Upvotes: 0

Related Questions