How to go from long to wide structure in HIVE?

Question

I have a hive data that looks like this data in long format

I'd like to restructure it to look like this data in wide format

What I have looks like this my latest attempt

This is the code that I've used. I'd like to get rid of all the NULL's and consolidate all the months into a single row under single year.

select carrier, year, month,

max(case when month =1 then quantity end) as jan,
max(case when month =2 then quantity end) as feb,
max(case when month =3 then quantity end) as mar,
max(case when month =4 then quantity end) as apr,
max(case when month =5 then quantity end) as may,
max(case when month =6 then quantity end) as jun,
max(case when month =7 then quantity end) as jul,
max(case when month =8 then quantity end) as aug,
max(case when month =9 then quantity end) as sep,
max(case when month =10 then quantity end) as oct,
max(case when month =11 then quantity end) as nov,
max(case when month =12 then quantity end) as dec

from (select final_month.*, row_number() over 
(partition by carrier, year order by carrier, year) from final_month) 
final_month group by carrier, year, month;

Here are all the references that I've been using link link

Thank you!

http://hadoopmania.blogspot.com/2015/12/transposepivot-table-in-hive.html?m=1 — samkart, Dec 12 '19 at 17:53
I was basing my script on this one, but I couldn't really work out group_map, so it'd be great if there's step-by-step explanation somewhere. — CeC, Dec 13 '19 at 17:15

score 0 · Accepted Answer · answered Dec 12 '19 at 04:46

Remove month from the select and group by. And I believe you want SUM, not max(), but you know the data better of course:

select carrier, year, 
sum(case when month =1 then quantity end) as jan,
sum(case when month =2 then quantity end) as feb,
sum(case when month =3 then quantity end) as mar,
sum(case when month =4 then quantity end) as apr,
sum(case when month =5 then quantity end) as may,
sum(case when month =6 then quantity end) as jun,
sum(case when month =7 then quantity end) as jul,
sum(case when month =8 then quantity end) as aug,
sum(case when month =9 then quantity end) as sep,
sum(case when month =10 then quantity end) as oct,
sum(case when month =11 then quantity end) as nov,
sum(case when month =12 then quantity end) as dec
from ...
group by carrier, year

How to go from long to wide structure in HIVE?

1 Answers1