看到個不常見的問題~然后在 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╰)╮