SQL垂直表到水平查询

您对main和stuff查询都使用了相同的别名。

create table dat_desk_employee (fk_desk int, fk_employee int);
insert into dat_desk_employee values
(1000, 2),(1000, 10),(1000, 23),(1000, 34),(1000, 55),
(1001, 3),(1001, 10),(1001, 23),(1001, 35),(1001, 60);
GO

10 rows affected

select fk_employee,
       stuff((SELECT distinct ', ' + cast(fk_desk as varchar(10))
              from dat_desk_employee t
              where t.fk_employee = e.fk_employee
       FOR XML PATH('')),1,1,'') fk_desks
from dat_desk_employee e
group by fk_employee
order by fk_employee asc 
GO

fk_employee | fk_desks   
----------: | :----------
          2 |  1000      
          3 |  1001      
         10 |  1000, 1001
         23 |  1000, 1001
         34 |  1000      
         35 |  1001      
         55 |  1000      
         60 |  1001      

D小提琴 here

你可以试试这个

DECLARE @Table TABLE (fk_desk VARCHAR(100), fk_employee VARCHAR(100))

INSERT INTO @Table VALUES ('1000', '1')
INSERT INTO @Table VALUES ('1001', '1')
INSERT INTO @Table VALUES ('1000', '2')
INSERT INTO @Table VALUES ('1001', '2')
INSERT INTO @Table VALUES ('1002', '2')
INSERT INTO @Table VALUES ('1003', '3')

SELECT 
    fk_employee
    , STUFF((
          SELECT ',' + T.fk_desk
          FROM @Table T WHERE A.fk_employee = T.fk_employee
          FOR XML PATH('')), 1, 1, '') as fk_desk
FROM @Table A
GROUP BY A.fk_employee

这一问题之前已经得到了回答: How to make a query with group_concat in sql server