DECLARE @s VARCHAR(100),@sql VARCHAR(1000)

SET @s='1,2,3,4,5,6,7,8,9,10'

一：
SET @sql='select col='''+ replace(@s,',',''' union all select ''')+''''

PRINT @sql

EXEC (@sql)
二：
SELECT B.id

FROM

( SELECT [value] = CONVERT(XML , '<v>' + REPLACE(@s , ',' , '</v><v>') + '</v>') ) A

OUTER APPLY ( SELECT  id = N.v.value('.' , 'varchar(100)') FROM A.[value].nodes('/v') N ( v ) ) B