>I have one table that has a workcenter field and a description field(unique records).
>
>Example:
>
>wkc1 wkc1description
>wkc2 wkc2description
>wkc3 wkc3description
>wkc14 wkc4description
>
>
>My second table has a userid field and a workcenter monitor field. The work center monitor field is a delimited string
>
>SDE wkc1
>XYZ wkc2,wck4
>ZYX wkc1,wkc2,wkc4
>
>How can I join these tables so I get a list of all workcenters and the userid(where there is a match) that is monitoring that workcenter.
>The following on gives a match where there is only one workcenter in the second table workcenter monitor field.
>
>

>
>testconnString = "Select Distinct amflib7.routng.wkctr, wcdsc From amflib7.routng" +;
>" Join amflib7.wrkctr On amflib7.wrkctr.wkctr = amflib7.routng.wkctr" +;
>" Join webprddt7.resmngt10 On webprddt7.resmngt10.userp8 = amflib7.routng.wkctr" +;
>" Where amflib7.routng.wkctr Not In('RMS','NOTES','INSP') Order By wcdsc"
>
>

The problem is that your data is not normalized. You first need to split the data into single code in WorkCenter field.

Using any of the splitting functions available (http://www.sommarskog.se/arrays-in-sql-perftest.html ) you can do

;with SplittedResults as (select T.UserID, T,WorkCenter, F.Value as Center from myTable T cross apply fnSplit(T.WorkCenter) F)

select ... JOIN SplittedResults SR on .. = SR.Center

I have this function to split strings:

CREATE FUNCTION [dbo].[fnSplit]
(@list  VARCHAR(8000),

 @delim CHAR(1) = ','
) 

RETURNS TABLE AS
RETURN
   WITH csvtbl(START, stop) AS (
     SELECT START = 1,
            stop = CHARINDEX(@delim COLLATE Slovenian_BIN2, @list + @delim)
     UNION ALL
     SELECT START = stop + 1,
            stop = CHARINDEX(@delim COLLATE Slovenian_BIN2,
                             @list + @delim, stop + 1)
     FROM   csvtbl
     WHERE  stop > 0
  )

  SELECT row_number() over (order by Start) as Position, LTRIM(RTRIM(SUBSTRING(@list, START,
                      CASE WHEN stop > 0 THEN stop - START ELSE 0 END)))
         AS VALUE
  FROM   csvtbl
  WHERE  stop > 0


GO