title: SQL Server - list the number of occurrences for specific strings within a column
body: <p>I have a Microsoft SQL Server 2008 database that has a table with multiple entries in one varchar column which are comma separated.</p>
  
  <p>As an example:</p>
  
  <pre><code>----------------------
  | Resource_List      |
  ----------------------
  | Danny              |
  | Gavin, Danny       |
  | Bob, Gavin, Mark   |
  | Bob                |
  | Bob                |
  ----------------------
  </code></pre>
  
  <p>I'm trying to construct a Query that counts the number of times these specific strings existing:</p>
  
  <p>An example of the output I'm after is:</p>
  
  <pre><code>----------------------------------
  | Name               |   Count   |
  ----------------------------------
  | Danny              |     2     |
  | Gavin              |     2     |
  | Mark               |     1     |
  | Bob                |     3     |
  ----------------------------------
  </code></pre>
  
  <p>The names in the <code>Resource_List</code> 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. </p>
  
  <p>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.</p>
  
  <p>Any help in how to construct the query would be appreciated.</p>
  
  <p>Cheers.
  Courtenay</p>