0

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
user2573690
  • 5,493
  • 9
  • 43
  • 61

2 Answers2

1

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.

Gordon Linoff
  • 1,242,037
  • 58
  • 646
  • 786
  • I agree, having a comma separated value is definitely not something I like but this is what I am faced with in this scenario. Thanks for the code but I ended up finding something online that worked better for me, updated my original post. – user2573690 Dec 13 '13 at 21:22
0

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

hope after this you can manage .

Community
  • 1
  • 1
KumarHarsh
  • 5,046
  • 1
  • 18
  • 22