Transpose Hive SQL data from existing column

Question

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.

Possible duplicate of [How to transpose/pivot data in hive?](https://stackoverflow.com/questions/23025380/how-to-transpose-pivot-data-in-hive) — Gyanendra Dwivedi, Mar 12 '18 at 18:24

score 2 · Accepted Answer · answered Mar 12 '18 at 18:14

2

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;

answered Mar 12 '18 at 18:14

Gordon Linoff

1,242,037
58
646
786

Transpose Hive SQL data from existing column

1 Answers1