sql - 查询逗号分隔的 id 到逗号分隔的值

Question

我有 2 张 table

部门

ID  Dept
---------
1   HR
2   Accts
3   IT

员工

ID Name     Depts
-------------------
1  Kevin     2,1
2  Michelle  1
3  Troy      1,3
4  Rheesa    2,3,1

我正在使用 SQL 查询寻找如下所示的输出。

员工部门

ID Name      Depts
-------------------------
 1 Kevin     Accts,HR
 2 Michelle  HR
 3 Troy      HR,IT
 4 Rheesa    Accts,IT,HR

我尝试了以下将 s 与部门连接起来的方法，但结果仅为每个部门生成一行。如何使用查询获得上述结果？

select 
    name, depts, dept 
from 
    employee
CROSS APPLY  
    dbo.split_list(employee.depts ,',') split
inner join 
    dbo.department on  depts= split.value
order by 
    name

Answer 1

DECLARE @Departments TABLE 
(
  ID INT PRIMARY KEY, 
  Dept VARCHAR(32) NOT NULL UNIQUE
);

DECLARE @Employees TABLE
(
  ID INT PRIMARY KEY,
  Name NVARCHAR(64) NOT NULL,
  Depts VARCHAR(255) NOT NULL
);

INSERT @Departments VALUES 
  (1,'HR'),  (2,'Accts'),  (3,'IT');

INSERT @Employees VALUES
  (1,'Kevin','2,1'), (2,'Michelle','1'),
  (3,'Troy','1,3'),  (4,'Rheesa','2,3,1');

SELECT ID, Name, Depts = STUFF((SELECT ',' + d.Dept 
    FROM @Departments AS d
    INNER JOIN @Employees AS ei
    ON ',' + ei.Depts + ',' LIKE '%,' + CONVERT(VARCHAR(12), d.id) + ',%'
    WHERE ei.ID = e.ID
    ORDER BY Dept
    FOR XML PATH, 
    TYPE).value(N'/text().[1]', N'nvarchar(max)'), 1, 1, N'')
FROM @Employees AS e
ORDER BY ID;

结果与您所需的结果不太匹配，因为排序是确定的(按部门名称排序):

ID      Name        Depts
----    --------    ----
1       Kevin       Accts,HR
2       Michelle    HR
3       Troy        HR,IT
4       Rheesa      Accts,HR,IT

如果您希望它们按逗号分隔列表中的外观排序，只需更改:

ORDER BY Dept

致:

ORDER BY CHARINDEX( ',' + CONVERT(VARCHAR(12), d.id) + ',', ',' + ei.Depts + ',')

结果:

ID      Name        Depts
----    --------    ----
1       Kevin       Accts,HR
2       Michelle    HR
3       Troy        HR,IT
4       Rheesa      Accts,IT,HR -- this is the only one affected as it turns out

但是，实际上，您应该标准化您的数据库。这绝对是一场噩梦。