Count distinct records in one column with multiple values in another column

Question

I'm pretty sure this is an easy question, but I'm having trouble wording it.

I need to count the total number of values in one column based on distinct criteria in another column.

Example:

A CD

B ABC 

C AD

D A

Would yield:

A 3

B 1

C 2

D 2

Answer 1

First, you shouldn't be storing lists of things in a string.

But, sometimes one is stuck with this format. In your example, you seem to have a table with all possible values. If so you can use a join:

select e.col1, count(e2.col2)
from example e left join
     example e2
     on charindex(e.col1, e2.col2) > 0
group by e.col1;

Note: this counts rows containing the value rather. If multiple values appear in a single row, the query is a bit more complicated.