How to make pivot table using hql?

Question

Input: field

id  title view
id1  我    12
id1  好    23
id2  你    13
id3  你    20
id4  他    11

Output:

id    我   好   你   他
id1   12  23   NaN  NaN
id2   NaN NaN  13   NaN
id3   NaN NaN  20   NaN
id4   NaN NaN  NaN  11

I know how to use python to get this result. But I have no idea how to get this using hive-sql.

Answer 1

use conditional aggregation with case when expression

select 
 id, 
 max(case when title='我' then view end) as '我',
 max(case when title='好' then view end) as '好',
 max(case when title='你' then view end) as '你',
 max(case when title='他' then view end) as '他'
from tablename
group by id