SQL Server含逗号分隔的数据匹配维表

提示：扫一扫查出行
【扫一扫了解最新限行尾号】
复制提示

日常有时候导出数据需求时，数据列会遇到带有分隔符的ID，但又需要匹配维表（如下图所示）将ID变成名称。

这种情况可以采用“分隔-匹配-合并”的方法

具体代码为：

-- 准备工作1：创建事实表数据

CREATE TABLE #StudentSubject

(

StuID INT IDENTITY(1,1) NOT NULL,

StuSubject VARCHAR(500) NOT NULL

)

INSERT INTO #StudentSubject(StuSubject) VALUES ('')

INSERT INTO #StudentSubject(StuSubject) VALUES ('')

INSERT INTO #StudentSubject(StuSubject) VALUES ('2,3')

INSERT INTO #StudentSubject(StuSubject) VALUES ('2,3,4')

INSERT INTO #StudentSubject(StuSubject) VALUES ('1,2,3,4')

INSERT INTO #StudentSubject(StuSubject) VALUES ('')

-- 准备工作2：创建维表数据

CREATE TABLE #D_Subject

(

SubjectID INT IDENTITY(1,1) NOT NULL,

SubjectName VARCHAR(500) NOT NULL

)

INSERT INTO #D_Subject(SubjectName) VALUES ('语文')

INSERT INTO #D_Subject(SubjectName) VALUES ('数学')

INSERT INTO #D_Subject(SubjectName) VALUES ('英语')

INSERT INTO #D_Subject(SubjectName) VALUES ('体育')

SELECT * FROM #StudentSubject

SELECT * FROM #D_Subject

;

-- 1分离：将逗号分隔的StuSubject分离匹配StudentSubject获取标签

WITH CetSubject AS

(

SELECT StuID,

       CAST(LEFT(StuSubject, CHARINDEX(',', StuSubject + ',') - 1) AS NVARCHAR(100)) SubjectID,

       CAST(STUFF(StuSubject + ',', 1, CHARINDEX(',', StuSubject + ','), '') AS NVARCHAR(100)) Split

  FROM #StudentSubject

 UNION ALL

SELECT StuID,

       CAST(LEFT(Split, CHARINDEX(',', Split) - 1) AS NVARCHAR(100)) SIds,

       CAST(STUFF(Split, 1, CHARINDEX(',', Split), '') AS NVARCHAR(100)) Split

  FROM CetSubject

 WHERE split > ''

)

-- 2匹配 将分离后的数据逐行与维表匹配

SELECT CS.StuID,

       DS.SubjectName

  INTO #CetSubjectName

  FROM CetSubject CS

  LEFT JOIN #D_Subject DS ON DS.SubjectID = CS.SubjectID

 WHERE CS.SubjectID <> ''

OPTION (MAXRECURSION 0);

-- 3合并 将与维表匹配的结果用逗号分隔合并还原

SELECT StuID,

       STUFF((SELECT ',' + T.SubjectName FROM #CetSubjectName T WHERE T.StuID = T2.StuID FOR XML PATH('')),1,1,'') SubjectName

  FROM #CetSubjectName t2

 GROUP BY StuID

DROP TABLE #D_Subject

DROP TABLE #StudentSubject

DROP TABLE #CetSubjectName