How can I group by a comma delineated list within a row?
Situation: I have a view that shows me information on support tickets. Each ticket is assigned to an indefinite number of resources. It might have one name in the resource list, it might have 5.
I would like to aggregate by individual names, so:
| Ticket ID | resource list +-----------+---------- | 1 | Smith, Fred, Joe | 2 | Fred | 3 | Smith, Joe | 4 | Joe, Fred
| Name | # of Tickets +-----------+---------- | Fred | 3 | Smith | 2 | Joe | 3
I did not design the database, so I am stuck with this awkward resource list column.
I've tried something like this:
SELECT DISTINCT resource_list , Count(*) AS '# of Tickets' FROM IEG.vServiceIEG GROUP BY resource_list ORDER BY '# of Tickets' DESC
...which gives me ticket counts based on particular combinations, but I'm having trouble getting this one step further to separate that out.
I also have access to a list of these individual names that I could do a join from, but I'm not sure how I would make that work. Previously in reports, I've used
WHERE resource_list LIKE '%' + @tech + '%', but I'm not sure how I would iterate through this for all names.
This is my final query that gave me the information I was looking for:
select b.Item, Count(*) AS 'Ticket Count' from IEG.vServiceIEG a cross apply (Select * from dbo.Split(REPLACE(a.resource_list, ' ', ''),',')) b Group by b.Item order by 2 desc