CODEWITHSUNDEEP
sqlsql-servercount
user2573690
user2573690

Reputation: 6013

Count number of occurrences of keyword in comma separated column?

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

Upvotes: 0

Views: 1657

Answers (2)

KumarHarsh
KumarHarsh

Reputation: 5094

Nevermind all you need if one split function SQL query to split column data into rows

hope after this you can manage .

Upvotes: 0

Gordon Linoff
Gordon Linoff

Reputation: 1271003

You have a lousy, lousy data structure, but sometimes one must make do with that. You should have a separate table storing each pair product/whatever pair -- that is the relational way.

with prodref as (
      select 'product1' as prod union all
      select 'product2' as prod union all
      select 'product5' as prod union all
      select 'product7' as prod
     )
select p.prod, count(*)
from prodref pr left outer join
     product p
     on ','+p.col+',' like '%,'+pr.prod+',%'
group by p.prod;

This will be quite slow on a large table. And, the query cannot make use of standard indexes. But, it should work. If you can restructure the data, then you should.

Upvotes: 1

Related Questions