Transpose/Pivot table in Hive SQL

Question

I have a table in the following format:

id | question | value    
1  | Name?    | John
1  | City?    | London
1  | Country? | England
2  | Name?    | Lucy
2  | City?    | New York
2  | Country? | USA

I want the table to look like the following instead

id | name | city      | Country
1  | John | London    | England
2  | Lucy | New York  | USA

I want to do this using Hive SQL but I'm not sure how to? There doesn't seem to be a pivot function

Answer 1

You can use (key, value) pairs along with to_map() generic function in order to pivot as desired

SELECT id,
       kv['name'] AS name,
       kv['city'] AS city,
       kv['country'] AS country
  FROM (
        SELECT uid, to_map(key, value) kv
          FROM tab
         GROUP BY id
        ) q

Answer 2

Use aggregation with case statements (conditional aggregation):

select 
      id, 
      max(case when question='Name?' then value end) as name,
      max(case when question='City?' then value end) as city,
      max(case when question='Country?' then value end) as country
  from your_table
group by id;

Demo:

with your_table as (
select stack(6,
1  ,'Name?','John',
1  ,'City?','London',
1  ,'Country?','England',
2  ,'Name?','Lucy',
2  ,'City?','New York',
2  ,'Country?','USA'
) as (id,question, value )
)

select 
      id, 
      max(case when question='Name?' then value end) as name,
      max(case when question='City?' then value end) as city,
      max(case when question='Country?' then value end) as country
  from your_table
group by id;
  

Result:

id  name    city    country
1   John    London  England
2   Lucy    New York    USA