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