SQL Vertical Table to Horizontal Query

Question

So I have a table that contains multiple employee's assigned to a desk. I need help figuring out a way to show all of the desks for the employee.

Currently, I am using this query which seems close but it is pulling every desk.

I would like it to look something like this:

|fk_employee| fk_desk  |
|3          | 1000,1001|

Using SQL 2012

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

Please add your desired result and your DBMS (version included) — McNets, Mar 01 '18 at 18:11

score 0 · Accepted Answer · answered Mar 01 '18 at 18:19

You have used same alias for both,main and stuff query.

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

dbfiddle here

score 0 · Answer 2 · answered Mar 01 '18 at 18:28

you can try this

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

and this have been answered on this question before: How to make a query with group_concat in sql server

SQL Vertical Table to Horizontal Query

2 Answers2