SQL实现将数据表中的字段中的值按分隔符分成多列

SQL实现将数据表中的字段中的值按分隔符分成多列

原始表格：

工作需求：将上表的class_all字段的数据已分号进行分割，生成下表：

1. 建表

建表SQL：

CREATE TABLE [dbo].[test_table](
	[id] nvarchar(max)  NOT NULL,
	[class_all] nvarchar(max) NULL
)

2. 插入数据

insert into test_table VALUES ('1','111;222;333');
insert into test_table VALUES ('2','444;555');
insert into test_table VALUES ('3','');

3. 需求分析

根据class_all字段已分号进行分割，然后一个类别一个分组，并且前面是对应的id

4. 实现代码

只需要修改表名，查询字段1，分割字段，结果字段名 ，分隔符即可！！！

WITH 表名 
AS 
( 
	SELECT 
	查询字段1,
	结果字段名 = CAST(LEFT(REPLACE(分割字段, ' ', ''),CHARINDEX('分隔符',REPLACE(分割字段,' ', '') + '分隔符')-1) AS NVARCHAR(MAX)) ,
	Split = CAST(STUFF(REPLACE(分割字段, ' ', '') + '分隔符',1,CHARINDEX('分隔符',REPLACE(分割字段, ' ','') + '分隔符'), '') AS NVARCHAR(MAX))
	FROM dbo.表名 
	WHERE 分割字段 IS NOT NULL
UNION ALL
	SELECT       
	查询字段1,
	结果字段名 = CAST(LEFT(Split,CHARINDEX('分隔符', Split) - 1) AS NVARCHAR(MAX)) ,
	Split = CAST(STUFF(Split, 1, CHARINDEX('分隔符', Split), '') AS NVARCHAR(MAX))
	FROM 表名 
	WHERE Split > ''
)
SELECT 查询字段1,结果字段名 FROM 表名

5. 案例代码

WITH test_table -- 表名
AS 
( 
	SELECT 
	id,
	class = CAST(LEFT(REPLACE(class_all, ' ', ''),CHARINDEX(';',REPLACE(class_all,' ', '') + ';')-1) AS NVARCHAR(MAX)) ,
	Split = CAST(STUFF(REPLACE(class_all, ' ', '') + ';',1,CHARINDEX(';',REPLACE(class_all, ' ','') + ';'), '') AS NVARCHAR(MAX))
	FROM dbo.test_table
	WHERE class_all IS NOT NULL
UNION ALL
	SELECT       
	id,
	class = CAST(LEFT(Split,CHARINDEX(';', Split) - 1) AS NVARCHAR(MAX)) ,
	Split = CAST(STUFF(Split, 1, CHARINDEX(';', Split), '') AS NVARCHAR(MAX))
	FROM test_table
	WHERE Split > ''
)
SELECT id,class FROM test_table

6. 实现效果

效果如下：