sqlserver 逗号分隔的字段为多条数据

 

写一个函数：

ALTER function[dbo].[f_split](
@c varchar(max),@split varchar(2))
returns @t table(col varchar(50))
as
begin
while(charindex(@split,@c)<>0)
begin
insert @t(col) values (substring(@c,1,charindex(@split,@c)-1))
set @c=stuff(@c,1,charindex(@split,@c),'')
end
insert @t(col) values (@c)
return
end

实例：

SELECT id, a.* FROM BILL_ApplyPayMain b CROSS APPLY f_split(b.GeneragedPayMentIDs,',') AS a
WHERE id=282356