> >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.
;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.CenterI 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