0

We have a table of User ID's with a varchar column that turns out to be a list of foreign keys. Why is it a list and not a reference, because it has to be :/ 

USERID  CLASSES_ATTENDED
100099  01052013,02042013,04302013,06042013

A table does exist that matches these classes to their names, but we have no way of list the class name to the user id.

CLASS     NAME
01052013  MATH
02042013  SCIENCE
04302013  COMPUTERS
06042013  COOKING

The only solution available to me is to use an after insert trigger on the varchar CLASSES_ATTENDED column that will read each "key" one-by-one and then insert the USERID/CLASS records(s) in another table.

How do I de-sequence the list then execute one INSERT statement for each item within a trigger declaration?

I have SQLServer 2005, with a Management Studio non-SSIS installation.

Thanks in advance!

user2888449
  • 5
  • 4
  • I think you want the split function. Here is a reference . . . http://stackoverflow.com/questions/2507330/sql-server-split-operation. However, your question doesn't actually explain what you want to do. What is the question? – Gordon Linoff May 21 '14 at 00:40
  • Thanks for the link! My question is near the bottom: How do I de-sequence the list then execute one INSERT statement for each item within a trigger declaration? – user2888449 May 21 '14 at 00:49

2 Answers2

0

An alternative way to "desequence" the data is to use a join.

insert into ProperJunctionTable(userid, class)
    select t.userid, c.class
    from table t join
         classes c
         on ',' + t.classes_attended + ',' like '%,' + c.class + ',%';

However, it is likely that split() will perform better.

By the way, if you don't like user defined functions for some reason, you can implement split() using a recursive CTE.

Gordon Linoff
  • 1,242,037
  • 58
  • 646
  • 786
  • I'm not following why you're adding the the commas? My system doesn't seem to like the second set, the ones surrounding the c.class. – user2888449 May 21 '14 at 23:57
  • @user2888449 . . . The commas delimit the values so "10" won't match "100". They may not be necessary in this case. – Gordon Linoff May 22 '14 at 00:22
0

The CROSS APPLY clause did the trick!

SELECT U.USERID, T.item AS CLASS
FROM bad_table U
CROSS APPLY split(M.CLASSES, ',') AS T
user2888449
  • 5
  • 4