0

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

Would become:

| 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.

EDIT:

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
Sonny Childs
  • 580
  • 2
  • 13
  • 3
    You can't, easily. That is why normalization is recommended in all circumstances (okay, there might be one exception in 100,000). Store the values in a junction table, and your problem is solved. – Gordon Linoff Jun 11 '15 at 14:57
  • Join to your name-list table on ', '+[Name]+',' LIKE '%, '+[resource list]+',%' – Tab Alleman Jun 11 '15 at 14:57
  • Right, I didn't design this database and I understand the insanity of putting comma delineated content in a row(in most cases). I'm aware of the split function, but I'm not certain on how to leverage that here. – Sonny Childs Jun 11 '15 at 14:59

1 Answers1

1

Check this Post (Function Definition by Romil) for splitting strings into a table:

How to split string and insert values into table in SQL Server

Use it this way : 

select b.Item, Count(*) from IEG.vServiceIEG a
cross apply (
 Select * from dbo.Split (a.resource_list,',')
   ) b
Group by b.Item
order by 2 desc
Community
  • 1
  • 1
Abdul Rehman Sayed
  • 6,532
  • 7
  • 45
  • 74
  • Fantastic! I was extremely confused at first because I didn't have the `dbo.Split` function, but I found it in Romil's answer and this worked like a charm. Thanks! – Sonny Childs Jun 11 '15 at 15:16