wm_concat在行转列的时候非常有用,但在行转列的过程中的排序问题常常难以控制。
可见下面例子:
准备测试表: drop table t; create table t (n number,m number); insert into t values(1,1); insert into t values(5,3); insert into t values(3,3); insert into t values(6,5); insert into t values(7,2); insert into t values(2,2); insert into t values(0,1); insert into t values(11,1); insert into t values(15,3); insert into t values(13,3); insert into t values(16,5); insert into t values(17,2); insert into t values(12,2); insert into t values(10,1); commit;SQL> select * from t order by 2,1;
N M
———- ———- 0 1 1 1 10 1 11 1 2 2 7 2 12 2 17 2 3 3 5 3 13 3 15 3 6 5 16 5测试wm_concat后的顺序:
测试1: SQL> select m,wm_concat(n) from t group by m;M WM_CONCAT(N)
———- ——————————————————————————– 1 11,0,1,10 2 17,2,7,12 3 15,3,5,13 5 16,6可见wm_concat后的顺序并没有按大->小,或小->大的顺序。
测试2:
–参考网上一些解决思路 SQL> select m,wm_concat(n) 2 from (select n,m from t order by m,n ) 3 group by m;M WM_CONCAT(N)
———- ——————————————————————————– 1 0,11,10,1 2 2,17,12,7 3 3,15,13,5 5 6,16可见顺序问题还是没有解决
最终解决思路:
SQL> select m, max(r) 2 from (select m, wm_concat(n) over (partition by m order by n) r from t) 3 group by m ;M MAX(R)
———- ——————————————————————————– 1 0,1,10,11 2 2,7,12,17 3 3,5,13,15 5 6,16