将一列包含多个ID拆分多行

看到个不常见的问题~然后在 Inner Sql Server2008 里面找到一个思路。

如果下面的表结构，如何拆分多行并对应员工号呢？

首先创建测试表

CREATE TABLE Department(    ID INT IDENTITY(1,1) PRIMARY KEY,    DepartmentName VARCHAR(50) NULL,    Members NVARCHAR(500) NULL)CREATE TABLE Employee(    ID INT IDENTITY(1,1) PRIMARY KEY,    NAME NVARCHAR(50) NULL)INSERT INTO Department VALUES('行政','1,3,5,10,12'),('销售','42,23,35,40,22'),('市场','13,17,27,28,43')SELECT * FROM dbo.Department

Employee 表的数据直接用个 Sql Generator生成一下下就好了~

--然后搞个数字辅助表作备用CREATE TABLE #Num (ID INT)INSERT INTO #Num        ( ID )SELECT TOP 500 ROW_NUMBER() OVER (ORDER BY (SELECT 1)) FROM sys.objects a,sys.objects b

--为了方便看代码，用个CTE来写。;WITH CTE1 AS(SELECT a.ID,        a.DepartmentName,        a.Members,        b.ID AS Pos,        CHARINDEX(',',a.Members,b.ID) AS NextSplit    FROM dbo.Department a        INNER JOIN #Num b             ON SUBSTRING(',' + a.Members,b.ID,1) = ','            AND b.ID <= LEN(a.Members)),CTE2 AS(SELECT ID,DepartmentName,        CASE WHEN NextSplit <> 0 THEN SUBSTRING(Members,Pos,NextSplit - Pos)             ELSE RIGHT(Members,LEN(Members) - Pos + 1) END AS EmployeeID    FROM CTE1)SELECT a.* ,b.NAME    FROM CTE2 a        INNER JOIN dbo.Employee b ON a.EmployeeID = b.ID

PS: 上述语句其实优化空间是很大的，这里只是提供给一个方法~就暂时不纠结效率上罗~╭(╯3╰)╮