逗号列转表格

CREATE TABLE [dbo].[t](
[id] [int] IDENTITY(1,1) NOT NULL,
[p] [nvarchar](max) NULL
) ON [PRIMARY]
insert into t(p) values('a,b,c')
insert into t(p) values('d')
select * from t
declare @p varchar(max);
set @p='a,b,c';
select item=y.i.value('(./text())[1]','nvarchar(4000)'),a.id from (select x=CONVERT(xml,'<i>'+replace(p,',','</i><i>')+'</i>').query('.') ,id from t)as a cross apply x.nodes('i') as y(i);

转载于:https://www.cnblogs.com/huanglong1987/p/7077202.html