Oracle分拆合并表view plain /**************************************************************************************** Oracle分拆合并表 清算人:中國風(Roy)
* S8 E! M" B; [4 R6 Y0 l# V# B 日期:2011.11.02 *****************************************************************************************/
) t" x/ |% d. g* |" t" \" m /**——合并
$ w- O) X6 a9 [& w/ s ——模拟数据Col1 Col2 1 a 1 b 1 c 2 d 2 e 3 f **/
# H: T/ _2 Q6 [' J, ~8 r- l /**——生成结不美观
, x7 D# b l1 c0 k# `; k- P w0 {* x COL1 COL2 1 a,b,c 2 d,e 3 f **/# x8 y0 X, q0 l8 O5 O
/**oracle10g以上版本字符串函数wmsys.wm_concat**/
3 L( y+ \& o- ]- k' s$ Y+ J /**体例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)
0 f4 J9 @, o1 _* k select Col1,wmsys.wm_concat(Col2 ) as Col2 from tab group by Col1
, ?- ^5 ?7 g1 m; P /**oracle9i可以用connect by**/; q- i' z" T) Q$ Z% O5 [( ?2 F/ j8 [. w1 |
/**体例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' from dual union all select 2,'e' from dual union all select 3,'f' from dual) Y% Q- g. D/ u( g" f7 q9 f3 z
select Col1,substr(max(sys_connect_by_path(Col2,',')),2) Col2 from(select a.*,row_number()over(partition by Col1 order by Col1) rn from Tab a )
! H$ ~" T3 ~3 Z4 n group by Col1 start with rn=1 connect by rn-1=prior rn and Col1=prior Col1 order by Col1;; w+ T0 O; p0 g- \2 S, }; \
/**——分拆7 M/ w: n/ f5 r) r) |% B
——模拟数据Col1 Col2 1 a,b,c 2 d,e 3 f **/
w% p2 |" x9 J- _3 O1 m7 u! V /**——生成结不美观- F2 P6 K8 r9 \7 S6 C
COL1 COL2 1 a 1 b 1 c 2 d 2 e 3 f **/ /**体例1**/ with Tab as(select 1 as Col1,N'a,b,c' as Col2 from dual union all select 2,N'd,e' from dual union all select 3,N'f' from dual ), U8 _: V& V/ Y4 v4 \+ z& {
3 M4 U# Y O0 x3 i8 z SELECT Col1,substr(Col2,lev,instr(Col2||',',',',lev)-lev) as Col2 from Tab,(SELECT LEVEL lev FROM DUAL CONNECT BY LEVEL |