Using the DelimitedSplit8K function, you would end up with:

DECLARE @String1 varchar(8000),

@String2 varchar(8000);

SET @String1 = 'a,b,c,d';

SET @String2 = 'apple,boy,college,dog';

SELECT a.Item, b.Item

FROM dbo.DelimitedSplit8K(@string1, ',') a

JOIN dbo.DelimitedSplit8K(@string2, ',') b

ON a.ItemNumber = b.ItemNumber;

Here is the latest version of the Delimited Split Function