舉個栗子
a b 1
a b 2
a b 3
c d 4
c d 5
c d 6
<==>
a b 1,2,3
c d 4,5,6
行轉列 ==>
select
col1,
col2,
concat_ws(',',collect_set(col3))
from table_test
group by col1,col2;
列轉行 <==
select col1, col2, col5
from table_test a
lateral view explode(split(col3,',')) b AS col5
-- 列轉行并打標
select
split(b.value,':')[0] as dim_code
,split(b.value,':')[1] as value
from
(
select concat(
'tx_amt:',tx_amt
,',refd_amt:', refd_amt
,',create_ordr_cnt:',create_ordr_cnt
) as value
from sdm.sdm_f02_pay_sfjh_tx_sum_i_d
where dt='2021-07-16' and dim_code='00'
)t
lateral view explode(split(value,',')) b AS value
復制 dim_code value
1 tx_amt 395018772.92
2 refd_amt 2456561.43
3 create_ordr_cnt 10619312
