Databases
StackOverflow2013
Postsdbo
POSQL Query loop from another table
Body
 

Note that there are some explanatory texts on larger screens.

plurals
  1. POSQL Query loop from another table
    text
    copied!<p>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].</p> <p>So here is an example of the contents of one record column 'suppliers' in the [responses] table:</p> <pre><code>Supplier A, Supplier B, Supplier C, Supplier D </code></pre> <p>and so on.</p> <p>I would ultimately like to be able to view a list of all the answers descending like this with their counts:</p> <ul> <li>Supplier C: 16</li> <li>Supplier B: 14</li> <li>Supplier D: 8</li> <li>etc</li> </ul> <p>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</p> <pre><code>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] </code></pre> <p>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.</p> <p>Any ideas on how to get this results in a sortable list by which suppliers were selected the most?</p> <p>Thanks</p> <hr> <p>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.</p> <p><code>SELECT r.suppliers, COUNT(*) FROM responses AS r JOIN Suppliers s ON ','+CompanyName+',' LIKE '%,'+r.suppliers+',%' GROUP BY r.suppliers</code></p>
 

Querying!

 
Guidance
An individual column

Larger individual text columns get their own page to allow for proper reading.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload