SQL 列拆分

with CTE
as(	
SELECT A.id, B.value
FROM(
    SELECT id, value = CONVERT(xml,'<root><v>' + REPLACE([列名], ',', '</v><v>') + '</v></root>')
		FROM 表名
		where limit<>'*' and id=3
)A
OUTER APPLY(
    SELECT value = N.v.value('.', 'varchar(100)') FROM A.[value].nodes('/root/v') N(v)
)B
)


Select * from CTE



with roy as 
(select [id],[limit]=cast(left([limit],charindex(',',[limit]+',')-1) as nvarchar(100)),
	Split=cast(stuff([limit]+',',1,charindex(',',[limit]+','),'') as nvarchar(100)) from [Roles]
union all
select [id],[limit]=cast(left(Split,charindex(',',Split)-1) as nvarchar(100)),Split= cast(stuff(Split,1,charindex(',',Split),'') as nvarchar(100)) from Roy where split>''
)

　　

转载于:https://www.cnblogs.com/gxivwshjj/p/3421473.html