关于对key-value的数据行转化为列的sql操作

insert into table ${db_name}.uv_month_20190529 
select 
k_v[‘${date}‘] as date_id,
k_v[‘0‘] as  0h,
k_v[‘1‘] as  1h ,
k_v[‘2‘] as 2h,
k_v[‘3‘] as 3h,
k_v[‘4‘] as 4h,
k_v[‘5‘] as 5h,
k_v[‘6‘] as 6h,
k_v[‘7‘] as 7h,
k_v[‘8‘] as 8h,
k_v[‘9‘] as 9h,
k_v[‘10‘] as 10h,
k_v[‘11‘] as 11h,
k_v[‘12‘] as 12h,
k_v[‘13‘] as 13h,
k_v[‘14‘] as 14h,
k_v[‘15‘] as 15h,
k_v[‘16‘] as 16h,
k_v[‘17‘] as 17h,
k_v[‘18‘] as 18h,
k_v[‘19‘] as 19h,
k_v[‘20‘] as 20h,
k_v[‘21‘] as 21h,
k_v[‘22‘] as 22h,
k_v[‘23‘] as 23h
from (
select str_to_map(concat_ws(‘,‘,collect_list(concat(hour,‘:‘,uv)))) k_v 
from ${db_temp}.tmp_ol_xianxia_store_table1_${date} 
) t;

这段sql是将统计出来的每个小时的uv进行转置操作，得到最终的结果。