将一列包含多个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╰)╮

 

posted on 2014-09-27 14:10  神崎橙  阅读(688)  评论(0编辑  收藏  举报

导航