Row to column

select dt_month,valid_num,unvalid_num from test.test_xw_rowtocol

The data are as follows




Conversion requirements: Column name is required for row to column conversion, Following chart




One way: Useunion all Form


select dt_month ,'valid_num' as type ​ ,sum(valid_num) as num ​from
temp.temp_xw_rowtocol ​group by dt_month​ union all select dt_month
,'unvalid_num' as type ​ ,sum(unvalid_num) as num ​from temp.temp_xw_rowtocol
​group by dt_month​ Mode two: Uselateral view andstr_to_map



select a.dt_month ,add_t.type ,add_t.num from temp.temp_xw_rowtocol a lateral
view explode(str_to_map(concat('valid_num=',valid_num
,'&unvalid_num=',unvalid_num ),'&','=' ) ) add_t as type,num
Mode three: Consistent thinking and mode 2, A little simpler, Disadvantages can only includeexplode function( Column name defaultkey andvalue Can also be customized)


select explode(str_to_map(concat('valid_num=',valid_num
,'&unvalid_num=',unvalid_num ),'&','=' ) ) from temp.temp_xw_rowtocol a ;
ps: Mode 1: easy to use when there are few indicators, When the index of row to column conversion is relatively large, the code quantity will be relatively large, Maintenance difficulties

      Mode 2 greatly reduces the amount of duplicate code, Need familiaritylateral
view andstr_to_map,concat Equal function use,concat Main splicingkey-value String stored as, Such as using asql Handle more than ten indicators, When it is required to store each indicator value in the column in the later stage, Use mode 2 to reduce the number of codes10 times, Greatly improve code readability and maintainability, meanwhilekey Values can be customized.



Column switching
adoptgroup by
select a.dt_month ,sum(case when type = 'valid_num' then num end) as valid_num
,sum(case when type = 'unvalid_num' then num end) as unvalid_num from
temp.temp_xw_coltorow a group by a.dt_month
specificHIVE Function reference officialWIKI: Click to open the link
<https://cwiki.apache.org/confluence/display/Hive/LanguageManual+UDF#LanguageManualUDF-CollectionFunctions>