SQL Query loop from another table

Question

I have a comma delimited field in one table[responses] and I have another table with all the options that can exist in that Comma Delimited field [suppliers].

So here is an example of the contents of one record column 'suppliers' in the [responses] table:

Supplier A, Supplier B, Supplier C, Supplier D

and so on.

I would ultimately like to be able to view a list of all the answers descending like this with their counts:

Supplier C: 16
Supplier B: 14
Supplier D: 8
etc

I am currently getting these numbers with a clunky manual sub select that doesn't get the data in the layout that I would like and would be pretty lengthy as we have around 300 suppliers

select
   (select count(*) from dbo.responses) as TotalCount,
   (select count(*) from dbo.responses where [suppliers] like '%Supplier C%') as [Supplier C],
   (select count(*) from dbo.responses where [suppliers] like '%Supplier B%') as [Supplier B]

I don't have total control over how the data comes in (comma delimited field) and the [suppliers] table was something I manually created hoping I could somehow loop through those items and get a count.

Any ideas on how to get this results in a sortable list by which suppliers were selected the most?

Thanks

I feel like I'm very close, but right now it's only counting record where the 'supplier' is the only answer selected, and not tallying any that are part of the comma delimited list, so I feel like I have something wrong in the syntax regarding the commas.

SELECT r.suppliers, COUNT(*) FROM responses AS r JOIN Suppliers s ON ','+CompanyName+',' LIKE '%,'+r.suppliers+',%' GROUP BY r.suppliers

There is no SQL Server 2003. The choices are 2000, 2005, 2008, 2008r2 and 2012. Hopefully it's any of those, except for 2000 which would really limit your options. In all likelihood it's SQL Server 2005. — RBarryYoung
– RBarryYoung, Commented Aug 19, 2013 at 17:05
@RBarryYoung You are right..Sorry :-) It's SQL Server 2008 on a server 2003 box — bmcnally
– bmcnally, Commented Aug 19, 2013 at 17:43

Gordon Linoff · Accepted Answer · 2013-08-19 16:12:13Z

3

If you have a list of all available suppliers, you can do:

select ms.name, count(*)
from responses r join
     mastersuppliers s
     on ','+ms.name+',' like '%,'+r.suppliers+',%'
group by ms.name;

By the way,the need to do this emphasizes why you don't want to store this type of data in comma-delimited lists. There should be a separate association table.

With a bit more work, you can also use a recursive CTE to extract the values from the list, if there is no master list.

EDIT:

If you don't have a master list, you can try the CTE version, which is something like:

with cte as (
      select left(r.suppliers, charindex(',', r.suppliers+',')) as suppliername,
             substring(r.suppliers, charindex(',', r.suppliers+',') + 1, len(r.suppliers)) as rest,
             1 as which
      from responses
      union all
      select left(r.rest, charindex(',', r.rest+',')) as suppliername,
             substring(r.rest, charindex(',', r.rest+',') + 1, len(r.rest)) as rest,
             1 + level
      from cte
      where len(suppliername) > 0
     )
select suppliername, count(*)
from cte
group by suppliername;

edited Aug 19, 2013 at 16:12

answered Aug 19, 2013 at 15:42

Gordon Linoff

1.3m62 gold badges706 silver badges857 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

bmcnally Over a year ago

Thanks Gordon, I feel like this is the right route. Although I must have some sytax errors. The query will execute but I am getting no results. Here is what I am running select suppliers, count(*) from dbo.Worksheet1$ r join dbo.Sheet1$ s on ','+CompanyName+',' like '%,'+r.suppliers+',%' group by suppliers;

bmcnally Over a year ago

Sorry, I'm new to stack and trying to figure out how to add code snippets to comments

SELECT     r.suppliers, COUNT(*) FROM         responses AS r  JOIN                       Suppliers s ON ','+CompanyName+',' LIKE  '%,'+r.suppliers+',%' GROUP BY r.suppliers

This is running but only pulls back responses where the 'supplier' is the only answer. Is the syntax correct around the commas?

Gordon Linoff Over a year ago

@bmcnally . . . The problem may be that you have comma-space as the separator rather than just comma. Try replacing the commas in the queries with comma followed by a space.

Collectives™ on Stack Overflow

SQL Query loop from another table

1 Answer 1

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related