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

文章目录

  • SQL实现将数据表中的字段中的值按分隔符分成多列
    • 1. 建表
    • 2. 插入数据
    • 3. 需求分析
    • 4. 实现代码
    • 5. 案例代码
    • 6. 实现效果

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


原始表格

SQL实现将数据表中的字段中的值按分隔符分成多列_第1张图片

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


SQL实现将数据表中的字段中的值按分隔符分成多列_第2张图片

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. 实现效果


效果如下:


SQL实现将数据表中的字段中的值按分隔符分成多列_第3张图片

你可能感兴趣的