Joining against a CSV Column in SQL Server

Question

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?

Answer 1

Try this(I assume there won't be any spaces etc between , and numbers in the csv data):

SELECT [DESC], COUNT(b.CC6)
FROM dbo.tbl_custom_code_6 a
INNER JOIN dbo.Respondent b ON CHARINDEX(',' + a.code + ',', ',' + b.CC6) > 0
GROUP BY [DESC]