一列保存多个ID（将多个用逗号、特殊符号 隔开的ID转换成用逗号隔开的名称）

一列保存多个ID（将多个用逗号、特殊符号 隔开的ID转换成用逗号隔开的名称）

sql 代码如下

IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[Department]'))
DROP TABLE [dbo].Department
GO
--部门表
CREATE TABLE Department
(
    id int,
    name nvarchar(50)
)
INSERT INTO Department(id,name)
SELECT 1,'人事部'
UNION 
SELECT 2,'工程部'
UNION 
SELECT 3,'管理部'

SELECT * FROM Department


IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[Employee]'))
DROP TABLE [dbo].Employee
GO
--员工表
CREATE TABLE Employee
(
    id int, 
    name nvarchar(20),
    deptIds varchar(1000)
)
INSERT INTO Employee(id,name,deptIds)
SELECT 1,'蒋大华','1,2,3'
UNION
SELECT 2,'小明','1,3'
UNION 
SELECT 3,'小华','3,2'

SELECT * FROM Employee


IF  EXISTS (SELECT * FROM sys.objects WHERE object_id = OBJECT_ID(N'[dbo].[fun_SplitIds]'))
DROP FUNCTION [dbo].fun_SplitIds
GO
CREATE FUNCTION dbo.fun_SplitIds(
    @Ids nvarchar(1000)
)
RETURNS @t_id TABLE (id VARCHAR(36))
AS
BEGIN
    DECLARE @i INT,@j INT,@l INT,@v VARCHAR(36);
    SET @i = 0;
    SET @j = 0;
    SET @l = len(@Ids);
    while(@j < @l)
    begin
       SET @j = charindex(',',@Ids,@i+1);
       IF(@j = 0) set @j = @l+1;
       SET @v = cast(SUBSTRING(@Ids,@i+1,@j-@i-1) as VARCHAR(36));
       INSERT INTO @t_id VALUES(@v)
       SET @i = @j;
    END
    RETURN;
END
GO

SELECT *
FROM Employee AS E 
   OUTER APPLY fun_SplitIds(E.deptIds) AS DID
   LEFT JOIN Department AS D ON DID.ID=D.id;