Find primary key from one table in comma separated list

Question

I've been given the task at work of creating a report based on a very poorly designed table structure.

Consider the following two tables. They contain techniques that each person likes to perform at each gym. Keep in mind that a unique person may show up on multiple rows in the PERSONNEL table:

PERSONNEL
+-----+-----+-------+--------+-----------+
| ID  | PID | Name  | Gym    | Technique |
+-----+-----+-------+--------+-----------+
| 1   | 122 | Bob   | GymA   | 2,3,4     |
+-----+-----+-------+--------+-----------+
| 2   | 131 | Mary  | GymA   | 1,2,4     |
+-----+-----+-------+--------+-----------+
| 3   | 122 | Bob   | GymB   | 1,2,3     |
+-----+-----+-------+--------+-----------+

TECHNIQUES
+-----+------------+
| ID  | Technique  |
+-----+------------+
| 1   | Running    |
+-----+------------+
| 2   | Walking    |
+-----+------------+
| 3   | Hopping    |
+-----+------------+
| 4   | Skipping   |
+-----+------------+

What I am having trouble coming up with is a MSSQL query that will reliably give me a listing of every person in the table that is performing a certain technique.

For instance, let's say that I want a listing of every person that likes skipping. The desired results would be:

PREFERS_SKIPPING
+-----+-------+--------+
| PID | Name  | Gym    |
+-----+-------+--------+
| 122 | Bob   | GymA   |
+-----+-------+--------+
| 131 | Mary  | GymA   |
+-----+-------+--------+

Likewise hopping:

PREFERS_HOPPING
+-----+-------+--------+
| PID | Name  | Gym    |
+-----+-------+--------+
| 122 | Bob   | GymA   |
+-----+-------+--------+
| 122 | Bob   | GymB   |
+-----+-------+--------+

I can break out the strings easily in ColdFusion, but that isn't an option due to the size of the PERSONNEL table. Can anyone help?

Take a look at the accepted answer to http://stackoverflow.com/questions/5611715/where-value-in-column-containing-comma-delimited-values I think that might be the starting point for a decent solution for you. — Glenn Stevens, Jun 07 '13 at 21:04
Maybe this can be useful http://stackoverflow.com/a/15038078/1699210 — bummi, Jun 07 '13 at 21:07
I know not to do it... this is a table structure that the people that originally set up the software created. It's so ingrained within the application, we're reluctant to change it. It looks like the first link will work fine, although at first glance I thought it wouldn't. — Lawson, Jun 07 '13 at 21:09

score 2 · Answer 1 · answered Jun 07 '13 at 21:29

Using this function

Create FUNCTION F_SplitAsIntTable 
(
@txt varchar(max)
)
RETURNS 
@tab TABLE 
(
 ID int
)
AS
BEGIN
    declare @i int
    declare @s varchar(20)
    Set @i = CHARINDEX(',',@txt)
    While @i>1
        begin
          set @s = LEFT(@txt,@i-1)
          insert into @tab (id) values (@s)
          Set @txt=RIGHT(@txt,Len(@txt)-@i)
          Set @i = CHARINDEX(',',@txt)
        end
    insert into @tab (id) values (@txt) 
    RETURN 
END

You can query like this

declare  @a Table  (id int,Name varchar(10),Kind Varchar(100))
insert into @a values (1,'test','1,2,3,4'),(2,'test2','1,2,3,5'),(3,'test3','3,5')

Select a.ID,Name
from @a a
cross apply F_SplitAsIntTable(a.Kind) b 
where b.ID=2

SOfanatic · Accepted Answer · 2013-06-08T13:41:04.723

2

I think this query looks cleaner:

SELECT p.*, 
t.Technique as ParsedTechnique
FROM Personnel p
JOIN Techniques t
ON CHARINDEX((','+CAST(t.id as varchar(10))+','), (','+p.technique+',')) > 0
WHERE t.id ='1';

You can just change the WHERE t.id = to whatever TechniqueId you need.

Fiddle Here

edited Jun 08 '13 at 13:41

answered Jun 07 '13 at 21:34

SOfanatic

5,523
5
36
57

Unfortunately, using that returns matches for t.id = 11 when I set it to t.id = 1. – Lawson Jun 08 '13 at 11:47
See my edit, you can bypass that by appending a comma to the beginning and end of the `id` and the `technique` column. This definitely works as we use it at work all the time because of some poor DB design. – SOfanatic Jun 08 '13 at 12:56
That version works, and it's very clean. I'll give you the credit. – Lawson Jun 08 '13 at 22:00

score 0 · Answer 3 · answered Jun 07 '13 at 21:54

One of the problems you have to prevent is prevent "1" from matching "10" and "11". For this, you want to be sure that all values are delimited by the separator (in this case a comma).

Here is a method using like that should work effectively (although performance will not be so great):

SELECT p.*, t.Technique as ParsedTechnique
FROM Personnel p join
     Techniques t
     on ','+p.technique+',' like '%,'+cast(t.id as varchar(255))+',%'
WHERE t.id = 1;

If performance is an issue, then fix your data structure an include a PersonTechniques table so you can do a proper join.

score 0 · Answer 4 · answered Jun 08 '13 at 11:51

The first comment under the question provided the link to the answer. Here's what I ended up going with:

WHERE
   p.Technique LIKE '%,29,%' --middle
      OR
   p.Technique LIKE '29,%' --start
      OR
   p.Technique LIKE '%,29' --end
      OR 
   p.Technique =  '29' --single (good point by Cheran S in comment)

At initial glance I thought it wouldn't work, but clever use of % made it not match ids like 129, etc.

Find primary key from one table in comma separated list

4 Answers4

Linked