sql server - 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?

Answer 1

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