12

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?

Dale K
  • 25,246
  • 15
  • 42
  • 71
bumble_bee_tuna
  • 3,533
  • 7
  • 43
  • 83

1 Answers1

10

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]
Chandu
  • 81,493
  • 19
  • 133
  • 134
  • Virtual High Five, Very Cool ... I didn't know about this function in SQL. This is comparing equality like contains in C# ? So that would also mitigate the issue of the leading value having no predicate comma ? Thanks again – bumble_bee_tuna Jul 15 '11 at 02:38
  • Contains is similar to the `IN` clause. To avoid the issue with the missing predicate comma you can see that I am prefixing b.CC6 with a comma (`',' + b.CC6`) – Chandu Jul 15 '11 at 02:40