Comma string

Question

I want to return the signature results from my table to a comma delimited string using T-SQL

THIS I WANT

CREATE TABLE SCRIPT

CREATE TABLE [dbo].[EVENT_TABLE](
    [ID_PERSON] [bigint] IDENTITY(1,1) NOT NULL,
    [ID_SIGNATURE] [bigint] NULL,
    [Name] [varchar](250) NULL,
    [Surname] [varchar](250) NULL,
    [Age] [varchar](250) NULL,
    [Department] [varchar](200) NULL,
 CONSTRAINT [PK_PERSON_TABLE] PRIMARY KEY CLUSTERED 
(
    [ID_PERSON] ASC
)WITH (PAD_INDEX  = OFF, STATISTICS_NORECOMPUTE  = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS  = ON, ALLOW_PAGE_LOCKS  = ON) ON [PRIMARY]
) ON [PRIMARY]

GO

SELECT SCRIPT

SELECT
DISTINCT
A.Department,
CASE 
WHEN SUM(A.PEOPLE) < 5 THEN '1 TO 4'
WHEN SUM(A.PEOPLE) >= 5 AND  A.PEOPLE < 10 THEN '5 TO 9'
WHEN SUM(A.PEOPLE) >= 10 THEN '10...N'
END
FROM
(
SELECT DISTINCT ID_SIGNATURE, Department, COUNT(NAME) AS PEOPLE
FROM EVENT_TABLE
GROUP BY ID_SIGNATURE, Department
) AS A
GROUP BY A.Department, A.PEOPLE

DATA

RESULT

Answer 1

Hope this is what you need. There are two many undesirable things in your code, for example Distinct, Aggregate. Try it.

;WITH cte
     AS (SELECT A.Department,
                CASE
                  WHEN A.PEOPLE < 5 THEN '1 TO 4'
                  WHEN A.PEOPLE >= 5 AND A.PEOPLE < 10 THEN '5 TO 9'
                  WHEN A.PEOPLE >= 10 THEN '10...N'
                END [Range],
                ID_SIGNATURE
         FROM   (SELECT ID_SIGNATURE,
                        Department,
                        Count(NAME) AS PEOPLE
                 FROM   EVENT_TABLE
                 GROUP  BY ID_SIGNATURE,
                           Department) AS A)
SELECT Department,
       [Range],
       (SELECT Stuff((SELECT ',' + CAST(ID_SIGNATURE AS varchar(20)) 
                      FROM   cte a
                      WHERE  a.Department = b.Department
                             AND a.[Range] = b.[Range]
                      FOR XML PATH ('')), 1, 1, '')) Signature
FROM   cte B
GROUP  BY Department,
          [Range]