整理Oracle分拆合并表

飞雪2539

　　Oracle分拆合并表
/****************************************************************************************Oracle分拆合并表整理人：中國風(Roy)日期:2011.11.02*****************************************************************************************//**--合并--模拟数据Col1Col21a1b1c2d2e3f**//**--生成结果COL1 COL21 a,b,c2 d,e3 f **//**oracle10g以上版本字符串函数wmsys.wm_concat**//**方法1**/with Tabas(select 1 as Col1,'a'  as Col2 from dual union allselect 1,'b' from dual  union allselect 1,'c' from dual union allselect 2,'d' from dual union allselect 2,'e' from dual union allselect 3,'f' from dual )selectCol1,wmsys.wm_concat(Col2 ) as Col2from tab  group by Col1/**oracle9i可以用connect by**//**方法2**/with Tabas(select 1 as Col1,'a'  as Col2 from dual union allselect 1,'b' from dual  union allselect 1,'c' from dual union allselect 2,'d' from dual union allselect 2,'e' from dual union allselect 3,'f' from dual )select Col1,substr(max(sys_connect_by_path(Col2,',')),2) Col2from (select a.*,row_number()over(partition by Col1 order by Col1) rn from Tab a )group by Col1 start with rn=1connect by rn-1=prior rn and Col1=prior Col1order by Col1;/**--分拆--模拟数据Col1Col21a,b,c2d,e3f**//**--生成结果COL1 COL21 a1 b1 c2 d2 e3 f **//**方法1**/with Tabas(select 1 as Col1,N'a,b,c' as Col2  from dual union allselect 2,N'd,e' from dual union allselect 3,N'f'   from dual )SELECT Col1,substr(Col2,lev,instr(Col2||',',',',lev)-lev) as Col2from Tab ,(SELECT LEVEL lev FROM DUAL CONNECT BY LEVEL<=100)WHERE substr(','||Col2,lev,1)=',' /** 条件可换为 instr(','||Col2,',',lev)=lev**/order by Col1/**方法2REGEXP_SUBSTR(srcstr, pattern, position, occurrence, modifier)  __srcstr        ：检索字符串__pattern      ：匹配模式__position     ：搜索srcstr的起始位置（默认为1）__occurrence：搜索第几次出现匹配模式的字符串（默认为1）__modifier     ：检索模式（'i'不区分大小写进行检索；'c'区分大小写进行检索。默认为'c'。）  **/with Tabas(select 1 as Col1,N'a,b,c' as Col2  from dual union allselect 2,N'd,e' from dual union allselect 3,N'f'   from dual )SELECT Col1,REGEXP_SUBSTR(Col2,'[^,]+',1,lev)FROM Tab,(SELECT LEVEL lev FROM dual CONNECT BY LEVEL <= 100) bWHERE (LENGTH(Col2)-LENGTH(REPLACE(Col2,',','')))+1 >=levORDER BY Col1,lev

SQL Server 拆分合并表方法
点击打开链接