整理Oracle分拆合并表

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 拆分合并表方法點擊開啟連結