-- set up some sample data

DROP TABLE #t1

CREATE TABLE #t1 (Table1ID INT IDENTITY(1,1) PRIMARY KEY, DenormalisedString VARCHAR(8000))

INSERT INTO #t1 (DenormalisedString) VALUES ('Red,Green,Blue'),('Red,Green'),('Red,Blue'),('Green,Blue')

DROP TABLE #t2

CREATE TABLE #t2 (Table2ID INT IDENTITY(1,1) PRIMARY KEY, DenormalisedString VARCHAR(8000))

INSERT INTO #t2 (DenormalisedString) VALUES ('Red,White,Blue'),('Red,White'),('Red,Blue'),('White,Blue')

-- For testing this query

SELECT

t1.Table1ID, t1.DenormalisedString,

t2.Table2ID, t2.DenormalisedString,

CommonItems = STUFF(y.CommonItems,1,1,'')

FROM #t1 t1

CROSS JOIN #t2 t2

CROSS APPLY ( -- y

-- Resolve out the items and match up,

-- then pivot the matching items back into a comma-delimited string

SELECT ','+x2.Item

FROM dbo.DelimitedSplit8K(t2.DenormalisedString,',') x2

CROSS APPLY dbo.DelimitedSplit8K(t1.DenormalisedString,',') x1

WHERE x1.Item = x2.Item

ORDER BY x2.ItemNumber

FOR XML PATH('')

) y (CommonItems)

ORDER BY t1.Table1ID, t2.Table2ID

For function dbo.DelimitedSplit8K, see http://www.sqlservercentral.com/articles/Tally+Table/72993/[/url]