Oracle 多行合并一行方法
NOTE:特别声明一下内容转自网络
https://www.doczj.com/doc/ac3678062.html,/html/2008/how_to_do_string_aggregate_on_oracle. html
一、实现要求
初始化表：
create table t(i int,a varchar(10),d varchar(100));
insert into t(i,a,d) values('1','b','2008-03-27 10:55:42');
insert into t(i,a,d) values('1','a','2008-03-27 10:55:46');
insert into t(i,a,d) values('1','d','2008-03-27 10:55:30');
insert into t(i,a,d) values('2','z','2008-03-27 10:55:55');
insert into t(i,a,d) values('2','t','2008-03-27 10:55:59');
查询：
假如有如下表，其中各个i值对应的行数是不定的
SQL> select * from t;
I A D
---------- ---------- -------------------
1 b 2008-03-27 10:55:42
1 a 2008-03-27 10:55:46
1 d 2008-03-27 10:55:30
2 z 2008-03-27 10:55:55
2 t 2008-03-27 10:55:59
实现要求：
要获得如下结果，注意字符串需要按照D列的时间排序：
1 d,b,a
2 z,t
二、解决方案
这是一个比较典型的行列转换，有好几种实现方法
1.自定义函数实现
Sql代码
1.create or replace function my_concat(n number)
2.return varchar2
3.is
4.type typ_cursor is ref cursor;
5.v_cursor typ_cursor;
6.v_temp varchar2(10);
7.v_result varchar2(4000):= '';
8.v_sql varchar2(200);
9.begin
10. v_sql := 'select a from t where i=' || n ||' order
by d';
11. open v_cursor for v_sql;
12. loop
13. fetch v_cursor into v_temp;
14. exit when v_cursor%notfound;
15. v_result := v_result ||',' || v_temp;
16. end loop;
17. return substr(v_result,2);
18.end;
19.
20.SQL> select i,my_concat(i) from t group by i;
21.
22. I MY_CONCAT(I)
23.---------- --------------------
24. 1 d,b,a
25. 2 z,t
虽然这种方式可以实现需求，但是如果表t的数据量很大，i的值又很多的情况下，因为针对每个i值都要执行一句select，扫描和排序的次数和i的值成正比，性能会非常差。
2.使用sys_connect_by_path
Sql代码
1.select i,ltrim(max(sys_connect_by_path(a,',')),',') a
2.from
3.(
4.select i,a,d,min(d) over(partition by i) d_min,
5.(row_number() over(order by i,d))+(dense_rank() over (orde
r by i)) numid
6.from t
7.)
8.start with d=d_min connect by numid-1=prior numid
9.group by i;
从执行计划上来看，这种方式只需要扫描两次表，比自定义函数的方法，效率要高很多，尤其是表中数据量较大的时候：
3.使用wm_sys.wm_concat
这个函数也可以实现类似的行列转换需求，但是似乎没有办法做到直接根据另外一列排序，所以需要先通过子查询或者临时表排好序:
Sql代码
1.SQL> select i,wmsys.wm_concat(a) from t group by i;
2.
3.I WMSYS.WM_CONCAT(A)
4.---------- --------------------
5. 1 b,a,d
6. 2 z,t
7.
8.SQL> select i,wmsys.wm_concat(a)
9. 2 from
10. 3 (select * from t order by i,d)
11. 4 group by i;
12.
13. I WMSYS.WM_CONCAT(A)
14.---------- --------------------
15. 1 d,b,a
16. 2 z,t
执行计划上看，只需要做一次表扫描就可以了，但是这个函数是加密过的，执行计划并不能显示函数内部的操作。
其他一些方法:
https://www.doczj.com/doc/ac3678062.html,/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:2 196162600402
https://www.doczj.com/doc/ac3678062.html,/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:1 5637744429336
一：天翼手机有多个套餐，转一行显示（逗号隔开）：
select t.mdse_id,wmsys.wm_concat(distinct https://www.doczj.com/doc/ac3678062.html,)
from pm_swllb_bak_201011 t
group by t.mdse_id
二：天翼手机有多个套餐，转一行显示（多列）：
select serv_id,sum(decode(trim(jf_code),'2090',charge,0)) charge_2090,
sum(decode(trim(jf_code),'2276',charge,0)) charge_2276,
sum(decode(trim(jf_code),'2246',charge,0)) charge_2246
from pm_income_detail_201011
where serv_id='70363453'
group by serv_id
注意：
比如数据是这样子（其中charge是float类型）：
执行：
select serv_id,
sum(decode(trim(jf_code),'2246',charge,0)) charge_2246
from temp_test
where serv_id='58573091'
group by serv_id
结果会是什么呢？