--> 测试数据：[tb]
IF OBJECT_ID('[tb]') IS NOT NULL DROP TABLE [tb]
GO 
CREATE TABLE [tb]([SubCode] VARCHAR(3),[SbuName] VARCHAR(1))
INSERT [tb]
SELECT '001','x' UNION ALL
SELECT '002','x'
--------------开始查询--------------------------

SELECT DISTINCT SubCode =STUFF((SELECT ','+[SubCode] FROM [tb] WHERE [SbuName]=t.[SbuName] FOR XML PATH('')),1,1,''),[SbuName]
FROM [tb] AS t 
----------------结果----------------------------
/* 
SubCode        SbuName
-------------------------
001,002         x

*/

------解决方案--------------------

探讨


select distinct SubDepartment1Code,'all' as SubDepartment1Name from tblEmployee  where SubDepartment1Code !='' and SubDepartment1Name is not null 
union all 
select distinct SubDepartment1Code,SubD……