Alternate/simple way to pivot data

SET NOCOUNT ON
– Prepare sample data
DECLARE @table TABLE (Dept_ID INT, EMP_Name VARCHAR(30))

INSERT INTO @table (Dept_ID, EMP_NAME) VALUES (10, ‘Jack’)
INSERT INTO @table (Dept_ID, EMP_NAME) VALUES (10, ‘John’)
INSERT INTO @table (Dept_ID, EMP_NAME) VALUES (10, ‘Beth’)
INSERT INTO @table (Dept_ID, EMP_NAME) VALUES (20, ‘Mary’)
INSERT INTO @table (Dept_ID, EMP_NAME) VALUES (20, ‘Allen’)
INSERT INTO @table (Dept_ID, EMP_NAME) VALUES (20, ‘Diana’)
INSERT INTO @table (Dept_ID, EMP_NAME) VALUES (20, ‘Don’)

SELECT
DISTINCT t.Dept_ID,
STUFF((SELECT ‘, ‘ + t2.EMP_NAME FROM @table AS t2 WHERE t2.Dept_ID = t.Dept_ID FOR XML PATH(”)), 1, 2, ”) AS EMP_NAMES
FROM @table AS t
ORDER BY t.Dept_ID

Output is shown below:

Dept_ID EMP_NAMES
———– ————————
10 Jack, John, Beth
20 Mary, Allen, Diana, Don