ODPS的wm_concat函数在行转列的时候非常有用，但在行转列的过程中的排序问题常常难以控制。官网的order子句在3.6.2版本中用不了，所以简单写了个SQL来实现：
①创建测试表及数据
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
