wm_concat排序问题

wm_concat在行转列的时候非常有用，但在行转列的过程中的排序问题常常难以控制。

可见下面例子：
测试表数据：tb_spec

              SIGN                CODE

1           0001                    01
2           0001                    02
3           0001                    03
4           0001                    04
5           0002                    05
6           0002                    06
7           0001                    07
8           0001                    08
9           0002                    09

10         0002                    10
11         0001                    11
12         0001                    12
13         0002                    13
14         0002                    14
15         0001                    15
16         0001                    16
17         0002                    17
18         0001                    18
19         0001                    19
20         0001                    20

测试wm_concat后的顺序：
测试1：
SQL> select a.sign,wm_concat(a.code)

       from tb_spec a group by a.sign;

SIGN                       WM_CONCAT(A.CODE)
———- ——————————————————————————–
0001               01,20,19,18,16,15,12,11,08,07,04,03,02

0002               05,17,14,13,10,09,06

可见wm_concat后的顺序并没有按大->小，或小->大的顺序。

测试2：
–参考网上一些解决思路
SQL> select a.sign,wm_concat(a.code)
from (select b.sign,b.code from tb_spec order by b.code ) a
group by a.sign;

SIGN                      WM_CONCAT(A.CODE)
———- ——————————————————————————–
0001                01,20,19,18,16,15,12,11,08,07,04,03,02

0002                05,17,14,13,10,09,06

可见顺序问题还是没有解决

最终解决思路：
SQL> select b.sign, max(b.r)  code
        from
        (select
            a.sign,
            wm_concat(a.code) over (partition by a.sign  order by a.code) r 
        from tb_spec a 
        ) b 
    group by b.sign

SIGN                                           CODE
———- ——————————————————————————–
0001                  01,02,03,04,07,08,11,12,15,16,18,19,20

0002                  05,06,09,10,13,14,17

转载自：http://blog.163.com/shuzhen_an/blog/static/11939930420131019103351170/