Group by comma separated list of Ids in TSQL not working

1

I am working on creating a stored procedure. I have data in this format

InteractiionId(pk) EmployeeId(fk) Reasons

1                   107           66
2                   107           68
3                   107           66,69
4                   98           71
5                   98           71
6                   98           69,71,68 
7                   90           68 
8                   90           69,68 
9                   90           66,71,68

I need to find for each employee count of reasons like this

EmployeeID ReasonsCount

  107    66(2)
    107     68(1)
    107     69(1) 
    98     71(3)
    98     68(1)
    98     69(1)
    90     68(3)
    90     69(1)
    90     66(1)
    90     71(1)

I am trying to do it like this:

select IdEmployee, E.FirstName,E.LastName,  count(reasons), Reasons FROM Interaction I
 LEFT JOIN Employee E
 ON I.IdEmployee = E.Id


-- where
 --    IdEmployee = 95 OR 
--    IdEmployee = 98 OR
  --  IdEmployee = 107
group by  IdEmployee, E.FirstName,E.LastName, Reasons

but it is not working. Please suggest solution.

Thanks.

2

Try this

DECLARE @t TABLE( ID INT IDENTITY, EmployeeId int, data VARCHAR(50))
INSERT INTO @t(EmployeeId,data) SELECT 107,'66'
INSERT INTO @t(EmployeeId,data)SELECT 107, '68'
INSERT INTO @t(EmployeeId,data)SELECT 107, '66,69'
INSERT INTO @t(EmployeeId,data)SELECT 98 ,'71'
INSERT INTO @t(EmployeeId,data)SELECT 98 ,'71'
INSERT INTO @t(EmployeeId,data)SELECT 98 ,'69,71,68'
INSERT INTO @t(EmployeeId,data)SELECT 90 ,'68'
INSERT INTO @t(EmployeeId,data)SELECT 90, '69,68'
INSERT INTO @t(EmployeeId,data)SELECT 90 ,'66,71,68'
SELECT
EmployeeId,
O.splitdata ,count (O.splitdata) CountReason
FROM
(
SELECT *,
cast(''+replace(F.data,',','')+'' as XML) as xmlfilter from @t F
)F1
CROSS APPLY
(
SELECT fdata.D.value('.','varchar(50)') as splitdata
FROM f1.xmlfilter.nodes('X') as fdata(D)) O
group by EmployeeId, o.splitdata
order by EmployeeId

3

Whilst there are workarounds, you would be much better off storing the reason codes as one-per-row rather than as a composite comma-delimited list in a SINGLE column in [Interaction]

With a delimited list you have no data validation, no referential integrity, no indexes, and also likely to have bad performance trying to make a report where you have to consider each Reason individually (although if you only have a few hundred rows you won't have a problem).

If you must have it in this format then, personally, I would also create a "cache table" which duplicates the Reasons into a one-per-row table, for ease and speed of reporting.