SQL 将一个字段内用逗号分隔的内容分成多条记录

---恢复内容开始---

本文转http://www.mhzg.net/a/20117/2011789260293.html

由于业务需求，我们可能会把一串以分割符字符串数据放到一个字段，如我们在客户端处理拆分是很简单的，不过这样做效果不太好，怎么用SQL SERVER 2008 来解决这件事件哪？

 

   --参考  
  
 拆分表:  
  
   
  
 --> --> (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 
  
  */