分隔字段

if object_id('tb') is not null
drop table tb

---->建表
create table tb([name] varchar(4),[value] varchar(9))
insert tb
select '张三','1,2,3,4' union all
select '李四','a,s,d,f,g'


--1.2000/2005通用方法
SELECT 
a.[name],
[value]=SUBSTRING(a.[value],number,CHARINDEX(',',a.[value]+',',number)-b.number)
FROM tb a
JOIN master..spt_values b
  ON b.type='P' 
   --AND SUBSTRING(','+a.col,b.number,1)=',' --用此条件或下面的条件均可
   AND CHARINDEX(',',','+a.[value],number)=number
   
--2.2005以上新方法:
SELECT a.[name],b.[value]
FROM (SELECT [name],[value]=CAST('<v>'+REPLACE([value],',','</v><v>')+'</v>' AS xml) FROM tb) a
OUTER APPLY (SELECT [value]=T.C.value('.','varchar(50)') FROM a.[value].nodes('/v') AS T(C)) b



--> 查询结果
SELECT * FROM tb

--> 删除表格
DROP TABLE tb