SqlServer技巧：数据展开与合并

说明：如何用一句sql实现数据展开（一行变多行）与合并（多行变一行）

-- 数据准备
CREATE TABLE TB_TEST02
(
	ID INT NOT NULL,
	VAL VARCHAR(200), 
)
TRUNCATE TABLE TB_TEST02
INSERT INTO TB_TEST02
VALUES(1,‘AA,DD,EE‘)
INSERT INTO TB_TEST02
VALUES(2,‘RR,WW,AA‘)

SELECT * FROM TB_TEST02

需求：将VALUE中的AA项去掉，保留原来的颗粒度
-- 实现逻辑：
-- 方法1：直接REPLACE：‘AA,‘ or ‘,AA‘ 缺点：不支持拆解后复杂的操作
-- 方法2：两步：第一步--依据分隔符‘,‘拆解，一行变多行，然后过滤；第二步--依据ID合并，多行变一行，回归原来的维度。

-- 方法2：一句sql实现
--  使用WITH CTE简化对表A的引用，也可以使用临时表
WITH A AS(
	-- 拆解
	SELECT C.ID, A.VALUE
	FROM TB_TEST02 C
	OUTER APPLY dbo.SplitTextToTable(C.VAL,‘,‘) A --OUTER APPLY实现逐行运算
	WHERE A.VALUE<>‘AA‘ --过滤
) 
--合并
select A.ID,STUFF((SELECT ‘,‘+B.VALUE FROM A B WHERE A.ID=B.ID  FOR XML PATH(‘‘)), 1, 1, ‘‘)   --把A的别名为B
from A GROUP BY A.ID

结果：

--补充：

-- 函数SplitTextToTable：表值函数，实现将一行文本按分隔符拆分为多行
CREATE FUNCTION [dbo].[SplitTextToTable]
(
  @SplitString varchar(8000),
  @Separator varchar(10) = ‘,‘
)
RETURNS @SplitStringsTable TABLE
(
  [VALUE] varchar(1000)
)
AS
BEGIN
     DECLARE @CurrentIndex int;
     DECLARE @NextIndex int;
     DECLARE @ReturnText varchar(8000);
     SELECT @CurrentIndex=1;
     WHILE @CurrentIndex<=len(@SplitString)
     BEGIN
        SET @NextIndex=charindex(@Separator,@SplitString,@CurrentIndex);
        IF @NextIndex=0 OR @NextIndex IS NULL
             SET @NextIndex=len(@SplitString)+1;
         
        SET @ReturnText=substring(@SplitString,@CurrentIndex,@NextIndex-@CurrentIndex);
        INSERT INTO @SplitStringsTable([VALUE]) VALUES(@ReturnText);
        SET @CurrentIndex=@NextIndex+1;
     END
     RETURN;
END

SqlServer技巧：数据展开与合并