合并分拆表

--合并分拆表 /****************************************************************************************************************************************************** 合并分拆表数据    整理人：中国风(Roy)    日期:2008.06.06 ******************************************************************************************************************************************************/    --> --> (Roy)生成測試數據     if  not  object_id( 'Tab' )  is  null      drop  table  Tab Go Create  table  Tab([Col1]  int ,[Col2] nvarchar(1)) Insert  Tab select  1,N 'a'  union  all select  1,N 'b'  union  all select  1,N 'c'  union  all select  2,N 'd'  union  all select  2,N 'e'  union  all select  3,N 'f' Go    合并表:    SQL2000用函数:    go if object_id( 'F_Str' )  is  not  null      drop  function  F_Str go create  function  F_Str(@Col1  int ) returns  nvarchar(100) as begin      declare  @S nvarchar(100)      select  @S= isnull (@S+ ',' , '' )+Col2  from  Tab  where  Col1=@Col1      return  @S end go Select  distinct  Col1,Col2=dbo.F_Str(Col1)  from  Tab    go    SQL2005用XML:    方法1:    select       a.Col1,Col2=stuff(b.Col2.value( '/R[1]' , 'nvarchar(max)' ),1,1, '' ) from       ( select  distinct  COl1  from  Tab) a Cross  apply      ( select  COl2=( select  N ',' +Col2  from  Tab  where  Col1=a.COl1  For  XML PATH( '' ), ROOT( 'R' ), TYPE))b    方法2:    select       a.Col1,COl2= replace (b.Col2.value( '/Tab[1]' , 'nvarchar(max)' ), char (44)+ char (32), char (44)) from       ( select  distinct  COl1  from  Tab) a cross  apply      ( select  Col2=( select  COl2  from  Tab   where  COl1=a.COl1  FOR  XML AUTO, TYPE)                  .query( '<Tab>                  {for $i in /Tab[position()<last()]/@COl2 return concat(string($i),",")}                  {concat("",string(/Tab[last()]/@COl2))}                  </Tab>' )                  )b    SQL05用CTE:    ; with  roy  as ( select  Col1,Col2,row=row_number()over(partition  by  COl1  order  by  COl1)  from  Tab) ,Roy2  as ( select  COl1, cast (COl2  as  nvarchar(100))COl2,row  from  Roy  where  row=1  union  all  select  a.Col1, cast (b.COl2+ ',' +a.COl2  as  nvarchar(100)),a.row  from  Roy a  join  Roy2 b  on  a.COl1=b.COl1  and  a.row=b.row+1) select  Col1,Col2  from  Roy2 a  where  row=( select  max (row)  from  roy  where  Col1=a.COl1)  order  by  Col1  option  (MAXRECURSION 0)       生成结果: /* Col1        COl2 ----------- ------------ 1           a,b,c 2           d,e 3           f    (3 行受影响) */       拆分表:    --> --> (Roy)生成測試數據     if  not  object_id( 'Tab' )  is  null      drop  table  Tab Go Create  table  Tab([Col1]  int ,[COl2] nvarchar(5)) Insert  Tab select  1,N 'a,b,c'  union  all select  2,N 'd,e'  union  all select  3,N 'f' Go    SQL2000用辅助表: if object_id( 'Tempdb..#Num' )  is  not  null      drop  table  #Num go select  top  100 ID=Identity( int ,1,1)  into  #Num  from  syscolumns a,syscolumns b Select       a.Col1,COl2= substring (a.Col2,b.ID,charindex( ',' ,a.Col2+ ',' ,b.ID)-b.ID)  from       Tab a,#Num b where      charindex( ',' , ',' +a.Col2,b.ID)=b.ID  --也可用 substring(','+a.COl2,b.ID,1)=','       SQL2005用Xml:    select       a.COl1,b.Col2 from       ( select  Col1,COl2= convert (xml, '<root><v>' + replace (COl2, ',' , '</v><v>' )+ '</v></root>' )  from  Tab)a outer  apply      ( select  Col2=C.v.value( '.' , 'nvarchar(100)' )  from  a.COl2.nodes( '/root/v' )C(v))b             SQL05用CTE:    ; with  roy  as  ( select  Col1,COl2= cast ( left (Col2,charindex( ',' ,Col2+ ',' )-1)  as  nvarchar(100)),Split= cast (stuff(COl2+ ',' ,1,charindex( ',' ,Col2+ ',' ), '' )  as  nvarchar(100))  from  Tab union  all select  Col1,COl2= cast ( left (Split,charindex( ',' ,Split)-1)  as  nvarchar(100)),Split=  cast (stuff(Split,1,charindex( ',' ,Split), '' )  as  nvarchar(100))  from  Roy  where  split> '' ) select  COl1,COl2  from  roy  order  by  COl1  option  (MAXRECURSION 0)    生成结果: /* Col1        COl2 ----------- ----- 1           a 1           b 1           c 2           d 2           e 3           f */