Basically there are 50 Custom Criteria which were done as a CSV column that corresponded to a table.
So say column CC5 with values '2,6,7,12,8,3,13,1,5,11,'
against a table dbo.tbl_custom_code_5
with values
code desc
1 Wine
10 Diet Pepsi
11 Other Soft Drink
12 Coffee
13 Tea ....
And so it goes ... So I need to do a group/count something along the lines of ...
Select [desc], COUNT(b.CC6)
from dbo.tbl_custom_code_6 a
INNER JOIN dbo.Respondent b ON a.code = b.CC6
group by [desc]
which obviously won't work due to the CSV, so I tried the first thing that came to mind.
Select [desc], COUNT(b.CC6)
from dbo.tbl_custom_code_6 a
INNER JOIN dbo.Respondent b ON a.code like '%' + b.CC6 + ',%'
group by [desc]
which doesn't work and wouldn't work even if it did because 6 would come up for 16 etc...
I know there has to be a better way to do this. Any thoughts?