I have a column which stores data like this:

Product:  

 product1,product2,product5  

 product5,product7  

 product1 

What I would like to do is count the number of occurrences there are of product1, product2, etc. but where the record contains multiple products I want it to double count them.

So for the above example the totals would be:  

 product1: 2  

 product2: 1  

 product5: 2  

 product7: 1 

How can I achieve this?

I was trying something like this: 

```
    select count(case when prodcolumn like '%product1%' then 'product1' end) from myTable

```

This gets me the count for product1 appears but how do I extend this to go through each product?

I also tried something like this: 

```
    select new_productvalue, count(new_productvalue) from OpportunityExtensionBase 
    group by new_ProductValue

```

But that lists all different combinations of the products which were found and how many times they were found...

These products don't change so hard coding it is ok...

EDIT: here is what worked for me. 

```
WITH Product_CTE (prod) AS 
(SELECT
  n.q.value('.', 'varchar(50)')
    FROM (SELECT cast('<r>'+replace(new_productvalue, ';', '</r><r>')+'</r>' AS xml) FROM table) AS s(XMLCol)
      CROSS APPLY s.XMLCol.nodes('r') AS n(q)
    WHERE n.q.value('.', 'varchar(50)') <> '')
    SELECT prod, count(*) AS [Num of Opps.] FROM Product_CTE GROUP BY prod

```