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 
  */