Oracle分拆合并表
/****************************************************************************************
Oracle分拆合并表
整理人:中國風(Roy)
日期:2011.11.02
*****************************************************************************************/
/**--合并
--模拟数据
Col1 Col2
1 a
1 b
1 c
2 d
2 e
3 f
**/
/**--生成结果
COL1 COL2
1 a,b,c
2 d,e
3 f
**/
/**oracle10g以上版本字符串函数wmsys.wm_concat**/
/**方法1**/
with Tab
as
(
select 1 as Col1,'a' as Col2 from dual union all
select 1,'b' from dual union all
select 1,'c' from dual union all
select 2,'d' from dual union all
select 2,'e' from dual union all
select 3,'f' from dual
)
select
Col1,wmsys.wm_concat(Col2 ) as Col2
from tab group by Col1
/**oracle9i可以用connect by**/
/**方法2**/
with Tab
as
(
select 1 as Col1,'a' as Col2 from dual union all
select 1,'b' from dual union all
select 1,'c' from dual union all
select 2,'d' f