SQL Realize dividing the values in the fields in the data table into multiple columns according to the separator

The original form ：

Job requirements ： Add the class_all The data of the field has been divided by semicolons , Generate the following table ：

1. Build table

Build table SQL：

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

2. insert data

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

3. Demand analysis

according to class_all The field has been divided by semicolons , Then group by category , And the front is corresponding id

4. Implementation code

It just needs to be modified Table name , Query field 1, Split fields , Result field name , Separator that will do ！！！

WITH  Table name  
AS 
( 
	SELECT 
	 Query field 1,
	 Result field name  = CAST(LEFT(REPLACE( Split fields , ' ', ''),CHARINDEX(' Separator ',REPLACE( Split fields ,' ', '') + ' Separator ')-1) AS NVARCHAR(MAX)) ,
	Split = CAST(STUFF(REPLACE( Split fields , ' ', '') + ' Separator ',1,CHARINDEX(' Separator ',REPLACE( Split fields , ' ','') + ' Separator '), '') AS NVARCHAR(MAX))
	FROM dbo. Table name  
	WHERE  Split fields  IS NOT NULL
UNION ALL
	SELECT       
	 Query field 1,
	 Result field name  = CAST(LEFT(Split,CHARINDEX(' Separator ', Split) - 1) AS NVARCHAR(MAX)) ,
	Split = CAST(STUFF(Split, 1, CHARINDEX(' Separator ', Split), '') AS NVARCHAR(MAX))
	FROM  Table name  
	WHERE Split > ''
)
SELECT  Query field 1, Result field name  FROM  Table name 

5. Case code

WITH test_table --  Table name 
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. Realization effect

The effect is as follows ：