Search code examples
sqlsql-serverstringcountvarchar

SQL Server - list the number of occurrences for specific strings within a column

I have a Microsoft SQL Server 2008 database that has a table with multiple entries in one varchar column which are comma separated.

As an example:

----------------------
| Resource_List      |
----------------------
| Danny              |
| Gavin, Danny       |
| Bob, Gavin, Mark   |
| Bob                |
| Bob                |
----------------------

I'm trying to construct a Query that counts the number of times these specific strings existing:

An example of the output I'm after is:

----------------------------------
| Name               |   Count   |
----------------------------------
| Danny              |     2     |
| Gavin              |     2     |
| Mark               |     1     |
| Bob                |     3     |
----------------------------------

The names in the Resource_List are known so I don't mind if I have to specifically add them into the search query. But being able to dynamically determine the names would be more elegant.

So far as the table itself goes - its a third party product that I'm interrogating because the reporting on it is poor, so I don't have the option of normalising the data in the table to a 1:M relationship, outside of what can be dynamically done within a standard query.

Any help in how to construct the query would be appreciated.

Cheers. Courtenay

Solution

  • If you have a list of the values in a table, then you can do:

    select n.name, count(*)
from table t join
     Names n
     on ','+t.resource_list+',' like '%,'+n.name+',%'
group by n.name;