sql server单行拆分成多行

1、创建表

CREATE TABLE [dbo].[m_test] (
[ID] int IDENTITY(1,1) NOT NULL,
[Name] varchar(30) COLLATE Chinese_PRC_CI_AS NULL,
[VipType] varchar(255) COLLATE Chinese_PRC_CI_AS NULL
)
ON [PRIMARY]
GO;

 

2、插入数据

INSERT INTO [m_test]([ID], [Name], [VipType]) VALUES (11, '一口价', '24,23'); GO

 

3、查出数据

select id, name, viptype from m_test;

 

 

 4、拆分逗号

SELECT A.ID, B.value, a.name
FROM(
SELECT ID,name, viptype = CONVERT(xml,'<root><v>' + REPLACE(viptype, ',', '</v><v>') + '</v></root>') FROM m_test
)A
OUTER APPLY(
SELECT value = N.v.value('.', 'varchar(100)') FROM A.viptype.nodes('/root/v') N(v)
)B