0

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
Xohayb
  • 23
  • 3
  • 1
    There isn't any table definition here – jtate Oct 14 '18 at 21:15
  • @jtate I am new on stack overflow, I couldn't apply appropriate formatting so I pasted my table definitions as HTML tables. You can run the code snippets to view the table definition as well as my desired out put. – Xohayb Oct 14 '18 at 21:25
  • If you are using SQL Server 2017, there's [STRING_AGG](https://learn.microsoft.com/en-us/sql/t-sql/functions/string-agg-transact-sql?view=sql-server-2017). If not, there are plenty of workaround for old versions of SQL Server – Code Different Oct 14 '18 at 21:35
  • You really should check - this has been asked 1,000 times before, and gets exactly the same (correct) answer every time. And BTW the answer has done the job of creating the insert statements - it is so much easier for everyone if you add those into your question in the first place. – TomC Oct 14 '18 at 23:16

1 Answers1

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:

screenshot

aduguid
  • 3,099
  • 6
  • 18
  • 37