tsql - Query multiple values from one column into IN list in WHERE clause -

i have table that, our purposes here, has 2 columns, first represents group in second column. it's not conventional way group data, it's have work with. here test view of table:

mfr_code  |  group abc       |  abc,fff,xyz def       |  def,ggg ghi       |  ghi,ppp,rrr 

i need have join links mfr_code column , determines of individual mfr_codes in second column.

the table table joining this:

from_catcode  |  partno  |  mfr_code dorc          |  1234    |  abc 

once joined, need able produce table looks like:

from_catcode  |  partno  |  mfr_code dorc          |  1234    |  abc dorc          |  1234    |  fff dorc          |  1234    |  xyz 

i have been trying figure out how parse multivalued column, fear loop impractical because we're talking looping through each record of both tables, 1 inside other, tens of thousands of records.

any insight or direction of can study further on helpful.

you can split , join table in single statement. try below code

    declare @mrfdesc table (     mrf_code varchar(10), groups varchar(50) ) insert @mrfdesc values ('abc','abc,fff,xyz'), ('def','def,ggg'), ('ghi','ghi,ppp,rrr')  declare @mrfmajor table (from_catcode varchar(20) ,  partno int  , mfr_code varchar(10)) insert @mrfmajor values('dorc',1234,'abc')  select mmajor.from_catcode, mmajor.partno, subset.certs @mrfmajor mmajor join ( select mrf_code, ltrim(rtrim(m.n.value('.[1]','varchar(8000)'))) certs (select mrf_code,cast('<xmlroot><rowdata>' +                          replace(groups,',','</rowdata><rowdata>') + '</rowdata></xmlroot>' xml) x           @mrfdesc)t cross apply x.nodes('/xmlroot/rowdata')m(n) ) subset  on subset.mrf_code = mmajor.mfr_code 

sql fiddle demo