Return to Answer

db<>fiddle

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 
                ',' + CAST(id2.[DepartmentID] AS VARCHAR)
            FROM 
                source_data AS id2
            WHERE id.[GroupID] = id2.[GroupID]
            GROUP BY id2.[DepartmentID]
            FOR XML PATH(''), TYPE
        ).value('.', 'varchar(max)')
        ,1,1,''
    )
FROM 
    source_data id

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

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.

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.