SqlServer 将一条数据按照逗号分散成多条数据

1. 通过函数，将一条数据按照某个字符分割成多条

2.建立函数

Create table ZD_NUMS (N int)

Declare @i int 
set @i = 1 

while @i<100 
begin
    insert into ZD_NUMS(N) select @i
    set @i = @i + 1 
end 
 

Create FUNCTION [dbo].[fn_SplitTSQL] 
  (@String NVARCHAR(MAX), @Separator NCHAR(1) = N',') RETURNS TABLE
AS
RETURN 
  SELECT
--      N - LEN(REPLACE(LEFT(S, N), @Separator, '')) + 1 AS POS,
      ROW_NUMBER() OVER (ORDER BY S) AS POS,
      SUBSTRING(S, N, CHARINDEX(@Separator, S + @Separator, N) -N) AS ELEMENT
    FROM ( SELECT @String AS S ) AS D
         JOIN dbo.ZD_NUMS ON N <= LEN(S) AND SUBSTRING(@Separator + S, N, 1) = @Separator;

GO

3.例如 将一条数据‘1,2,3,4,5’ 用，分割成5行

select * from  [dbo].[fn_SplitTSQL]('1,2,3,4,5',',')