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 , As shown below




Mode 1 : use union all Form of


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 2 : use lateral view and str_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 include explode function ( Column name default key and value 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 , Difficult to maintain

      Mode 2 greatly reduces the amount of duplicate code , Need to be familiar with lateral
view and str_to_map,concat Equal function use ,concat Main splicing key-value String stored as , Such as using a sql 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 codes 10 times , Greatly improve code readability and maintainability , meanwhile key Values can be customized .



Column to row
adopt group 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
specific HIVE Function reference official WIKI: Click to open the link
<https://cwiki.apache.org/confluence/display/Hive/LanguageManual+UDF#LanguageManualUDF-CollectionFunctions>