ODPS的wm_concat函數在行轉列的時候非常有用抒巢，但在行轉列的過程中的排序問題常常難以控制。官網的order子句在3.6.2版本中用不了，所以簡單寫了個SQL來實現：
①創(chuàng)建測試表及數據
create table tianyc01(h BIGINT , l BIGINT , v string);
insert into tianyc01 values(1,1,'a');
insert into tianyc01 values(1,2,'b');
insert into tianyc01 values(1,3,'c');
insert into tianyc01 values(1,4,'d');
insert into tianyc01 values(1,5,'e');
insert into tianyc01 values(1,6,'f');
insert into tianyc01 values(2,1,'A');
insert into tianyc01 values(2,2,'B');
insert into tianyc01 values(2,3,'C');
insert into tianyc01 values(2,4,'D');
insert into tianyc01 values(2,5,'E');
insert into tianyc01 values(2,6,'F');
select * from tianyc01;
h l v
1 1 a
1 2 b
1 3 c
1 4 d
1 5 e
1 6 f
2 1 A
2 2 B
2 3 C
2 4 D
2 5 E
2 6 F
②行轉列
select h,
split_part(v,',',instr(l,'1')),
split_part(v,',',instr(l,'2')),
split_part(v,',',instr(l,'3')),
split_part(v,',',instr(l,'4')),
split_part(v,',',instr(l,'5')),
split_part(v,',',instr(l,'6'))
from(
select h,WM_CONCAT('',l) as l,WM_CONCAT(',',v) as v from tianyc01
group by h
) x
h _c3 _c4 _c5 _c6 _c7 _c8
------------------------------
1 a b c d e f
2? A B C D E F
