SQL数据处理，字符串先拆分后合并

数据库开发
目录

需求


以上数据样例，需要转为：

实际业务数据可能不止以上两列，而且数据量比较大，实现时要考虑性能。
本文用SQL实现。

思路

看上去是简单的字符串拆分，实际是每隔两个分隔符（\）进行拆分，而且要将没拆分的分隔符符号转为冒号（：）。可以考虑先全部拆分再每两个进行合并。

代码

/***  第一步，移除多余字符，方便后续折分   ***/
-- update invalid splitor, "/ -> / 
update dbo.physcial_checkup 
set check_result = replace(check_result,'"/','/') 
where charindex('"/',check_result) > 0;

-- remove first char / and last char \
update dbo.physcial_checkup 
set check_result = SUBSTRING(check_result,2,len(check_result) - 2);

/***  第二步，全部拆分为多行   ***/
-- split to temp table
select  a.id
       ,a.check_result
       ,b.[value] as check_result_split
	   ,CEILING(b.ID/2.0) as group_id  -- 每两个一组，方便后续合并
	   ,ROW_NUMBER() over(partition by a.id order by b.id) as rn
into #temp_split
from dbo.physcial_checkup  a
cross apply dbo.SplitString(check_result,'\') as b; 

/***  第三步，列值合并   ***/
select distinct
     a.id
	,stuff((select N'：' + check_result_split from #temp_split 
	        where id = a.id  and group_id = a.group_id 
			order by id,rn
			for xml path('')),1,1,'') as check_result
from #temp_split a;

说明

  • 以上第三步列值合并是在SQL Server 2017之前的常见写法，2017版本及之后有新函数STRING_AGG，性能应该会快很多。
  • 以上代码中的group_id是用来后续进行合并的辅助列，每连续两个ID同一组
  • 在不使用STRING_AGG的情况下，提升第三步的性能可以考虑：
    • 增加索引 create index (id,group_id) include (check_result_split)，这样子查询可走索引
    • 由于本案例是每个ID按相同Group_ID进行合并，且每个Group两行，合并规则非常固定，可考虑改变写法，只访问一次表即可，按行号分别将同组的两行转为列，最后再拼接：
	    /***  第三步，列值合并   ***/
		select id,group_id
		      ,max(case rn % 2 when 1 then check_result_split end)  + N'：' + 
		       max(case rn % 2 when 0 then check_result_split end) as check_result
		from #temp_split a
		group by id,group_id;
  • 第二步比较花时间，SplitString是字符串拆分为多行的函数，可参考此文