CODEWITHSUNDEEP
sql
bmcnally
bmcnally

Reputation: 3

SQL Query loop from another table

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:

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

Upvotes: 0

Views: 160

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269593

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;

Upvotes: 3

Related Questions