CODEWITHSUNDEEP
sqlhivetranspose
jumpman8947
jumpman8947

Reputation: 591

Transpose Hive SQL data from existing column

i'm currently using the following query to get the below results

select 
  mt.name,
  mt.title,
  mt.type,
  mt.day1,
  mt.day2

from mytable mt

gives me results like

name       title      type      day1              day2
batman     super      gothom    12/22/1990        2/2/1990
batman     super      dc        1/9/1990          7/5/1990

The new selection should create new columns based off type, and take the data from a specific column 'in this case day2' I ideally want my results to look like 

name       title      gothom          dc      
batman     super      2/2/1990        7/5/1990

How can i achieve the desired table above.

Upvotes: 1

Views: 173

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1271231

In Hive, you can pivot the data using conditional aggregation:

select mt.name, mt.title,
       max(case when type = 'gothom' then day2 end) as gothom,
       max(case when type = 'dc' then day2 end) as dc
from mytable mt
group by mt.name, mt.title;

Upvotes: 2

Related Questions