TSQL - Comma Separated Values for a specific column [duplicate]

Question

I am looking to solve a problem where I have SQL Table with the following table definition:

UniqueID    GroupID   DepartmentID
-----------------------------------
    1          2      D005
    2          2      D006
    3          2      D007
    4          5      D002
    5          1      D006
    6          3      D001
    7          3      D009
    8          3      D002
    9          3      D004
   10          3      D006

This table stores information of different departments that are part of a specific group.

I am looking for a simple and optimized query that outputs the list of departments as comma separated values(in the same row) against each group The output should be like this:

GroupID      DepartmentList
-----------------------------
   2         D005,D006,D007
   5         D002
   1         D006
   3         D001,D009,D002,D004,D006

Answer 1

I used the following table definition.

IF EXISTS(SELECT * FROM [sys].[objects] WHERE [object_id]=OBJECT_ID(N'Table1') AND [TYPE]=N'U')
DROP TABLE Table1
; 

CREATE TABLE Table1
(
  [UniqueID] [int]
, [GroupID] [int]
, [DepartmentID] [varchar](4) NULL
);

INSERT INTO Table1 ([UniqueID], [GroupID], [DepartmentID]) VALUES(1, 2, 'D005');
INSERT INTO Table1 ([UniqueID], [GroupID], [DepartmentID]) VALUES(2, 2, 'D006');
INSERT INTO Table1 ([UniqueID], [GroupID], [DepartmentID]) VALUES(3, 2, 'D007');
INSERT INTO Table1 ([UniqueID], [GroupID], [DepartmentID]) VALUES(4, 5, 'D002');
INSERT INTO Table1 ([UniqueID], [GroupID], [DepartmentID]) VALUES(5, 1, 'D006');
INSERT INTO Table1 ([UniqueID], [GroupID], [DepartmentID]) VALUES(6, 3, 'D001');
INSERT INTO Table1 ([UniqueID], [GroupID], [DepartmentID]) VALUES(7, 3, 'D009');
INSERT INTO Table1 ([UniqueID], [GroupID], [DepartmentID]) VALUES(8, 3, 'D002');
INSERT INTO Table1 ([UniqueID], [GroupID], [DepartmentID]) VALUES(9, 3, 'D004');
INSERT INTO Table1 ([UniqueID], [GroupID], [DepartmentID]) VALUES(10, 3, 'D006');

To run it in one statement I used a CTE with a select from values. Then I used the STUFF function to concatenate the string values.

WITH
source_data
AS
(
    SELECT Table1.* FROM (VALUES
      ( 1, 2, 'D005')
    , ( 2, 2, 'D006')
    , ( 3, 2, 'D007')
    , ( 4, 5, 'D002')
    , ( 5, 1, 'D006')
    , ( 6, 3, 'D001')
    , ( 7, 3, 'D009')
    , ( 8, 3, 'D002')
    , ( 9, 3, 'D004')
    , ( 10, 3, 'D006')
    ) Table1 ([UniqueID], [GroupID], [DepartmentID]) 
)
SELECT DISTINCT
      [GroupID]
    , [DepartmentList] = STUFF
    ( 
        (
            SELECT 
                ',' + id2.[DepartmentID] 
            FROM 
                source_data AS id2
            WHERE 
                id1.[GroupID] = id2.[GroupID]
            GROUP BY 
                id2.[DepartmentID]
            FOR XML PATH(''), TYPE
        ).value('.', 'varchar(max)')
        ,1,1,''
    )
FROM 
    source_data AS id1

db<>fiddle

Results: