MSSQL: How to create a VIEW that shows column as comma separated value

Question

I am using MSSQL database and I have a table named UserDepartments that has all the Departments ID that assigned to one user as the following

UserID | DepartmentID
1      | 220
1      | 330
1      | 210
1      | 100
2      | 330
2      | 440
3      | 550

what i want exactly to create a VIEW of that table that show it as the following

UserID | Departments
1      | 220, 330, 210, 100
2      | 330, 440
3      | 550

is it possible to do it using MSSQL?

possible duplicate [Output a comma-separated list in a column in SQL Server](http://stackoverflow.com/a/13053078/1699210) — bummi, Jul 16 '13 at 05:20
thanks man can you post it as an answer so i can mark it as an answer? — msytNadeem, Jul 16 '13 at 05:24
there is no new answer needed, this was anwered many times. You may vote on existing answers. Have fun. — bummi, Jul 16 '13 at 05:35

score 2 · Accepted Answer · answered Jul 16 '13 at 05:18

Try this one -

Query:

DECLARE @temp TABLE
(
       UserID INT
     , DepartmentID INT
)

INSERT INTO @temp (UserID, DepartmentID)
VALUES 
     (1, 220),(1, 330),
     (1, 210),(1, 100),
     (2, 330),(2, 440),
     (3, 550)

SELECT 
       t.UserID
     , DepartmentID = STUFF((
          SELECT ', ' + CAST(DepartmentID AS VARCHAR(10))
          FROM @temp t2
          WHERE t2.UserID = t.UserID
          FOR XML PATH(''), TYPE).value('.', 'VARCHAR(1024)'), 1, 2, '')                            
FROM ( 
     SELECT DISTINCT UserID 
     FROM @temp
) t

Output:

UserID      DepartmentID
----------- -----------------------------
1           220, 330, 210, 100
2           330, 440
3           550

this working fine as well – msytNadeem Jul 16 '13 at 05:26 — msytNadeem, Jul 16 '13 at 05:26

MSSQL: How to create a VIEW that shows column as comma separated value

1 Answers1