How to get list of employees in each department seperated by comma

Question

I have tried below one using XML Path but getting error.

Conversion failed when converting the varchar value ',' to data type int.

Can you correct the below query.

SELECT STUFF((SELECT ',' + COUNT(E.EMployeeId) FROM EMployee E WHERE E.DepartmentId IN(SELECT DepartmentId FROM Department) 
            FOR XML PATH(''), TYPE).value('.', 'VARCHAR(MAX)'),1,1,'')

Required Output

5,7,8,9

Conversion failed when converting the varchar value ',' to data type int. — Balanjaneyulu K, Nov 14 '17 at 07:25
@BalanjaneyuluK in order to avoid error message you should cast your `count` to varchar — Yogesh Sharma, Nov 14 '17 at 07:26

DineshDB · Answer 1 · 2017-11-14T11:13:21.877

1

Try the following:

The COUNT() Returns integer value, you can't merge int with varchar. So you have to convert the value to char.

SELECT STUFF((SELECT ',' + CAST(COUNT(E.EMployeeId) AS VARCHAR) FROM EMployee E WHERE E.DepartmentId IN(SELECT DepartmentId FROM Department) 
            FOR XML PATH(''), TYPE).value('.', 'VARCHAR(MAX)'),1,1,'')

Try this answer, If you face any issue, mention in the comments:

SELECT DISTINCT DepartmentId,
     STUFF(
        (
            SELECT ',' + CAST(COUNT(E.EMployeeId) AS VARCHAR) 
            FROM EMployee E 
            WHERE E.DepartmentId IN(SELECT DepartmentId FROM Department) 
                AND E.DepartmentId=T.DepartmentId
            FOR XML PATH(''), TYPE
            ), 1, 1, '') AS id_list
FROM EMployee as T

Try this one for, both department and COUNT() in Comma separated:

SELECT STUFF((SELECT ',' + CAST((DepartmentId)AS VARCHAR) 
FROM(
    SELECT DepartmentId,COUNT(EMployeeId) EMployeeId
    FROM EMployee E 
    WHERE E.DepartmentId IN(SELECT DepartmentId FROM Department) 
    GROUP BY DepartmentId
    )E
FOR XML PATH(''), TYPE).value('.', 'VARCHAR(MAX)'),1,1,'') DepID
    ,STUFF((SELECT ',' + CAST((EMployeeId)AS VARCHAR)
FROM(
    SELECT DepartmentId,COUNT(EMployeeId) EMployeeId
    FROM EMployee E 
    WHERE E.DepartmentId IN(SELECT DepartmentId FROM Department) 
    GROUP BY DepartmentId
    )E
FOR XML PATH(''), TYPE).value('.', 'VARCHAR(MAX)'),1,1,'')EmpCount

edited Nov 14 '17 at 11:13

answered Nov 14 '17 at 07:26

DineshDB

5,998
7
33
49

Hi dinesh, it' working but giving total employee count. I m looking for employee count in each department separated by comma. Thanks for ur help. – Balanjaneyulu K Nov 14 '17 at 07:36
How can we get list of employee count in each department – Balanjaneyulu K Nov 14 '17 at 08:08
Wait a few minutes, I'll check and tell you... – DineshDB Nov 14 '17 at 08:09
To get employee count for each department simply use, SELECT DepartmentId, count(*) as cnt FROM Employee Group By DepartmentId – Eralper Nov 14 '17 at 09:01
I have tried the same but I was trying to employee count in each department as a list – Balanjaneyulu K Nov 14 '17 at 10:28
Try the updated answer, hope this helps you – DineshDB Nov 14 '17 at 11:14

How to get list of employees in each department seperated by comma

1 Answers1