-- create CREATE TABLE MasterCriteriaTable ( MasterCriteriaRowID varchar(10), DetailCriteriaID varchar(100) ); Create Table DetailCriteriaTable( DetailCriteriaRowId varchar(10), SomeDetailInformation varchar(100) ); -- insert INSERT INTO MasterCriteriaTable VALUES ('1','1-3,5-8,13'), ('2','5,6,12,17-20,22-24'); INSERT INTO DetailCriteriaTable VALUES ('1', 'This is detail nr1'), ('2', 'This is detail nr2'), ('3', 'This is detail nr3'), ('4', 'This is detail nr4'), ('22', 'This is detail nr22 you get the idea'); -- fetch SELECT * FROM MasterCriteriaTable; SELECT * FROM DetailCriteriaTable; WITH MasterCriteria AS ( SELECT MasterCriteriaRowId, case when charindex('-',ss.value)=0 then ss.value else CAST(LEFT(ss.value, CHARINDEX('-', ss.value) - 1) AS INT) end AS CriteriaID, case when charindex('-', ss.value)=0 then ss.value else CAST(RIGHT(ss.value, LEN(ss.value) - CHARINDEX('-', ss.value)) AS INT) end AS MaxCriteriaID from (select value, MasterCriteriaRowID from MasterCriteriaTable cross apply string_split(DetailCriteriaID,',')) ss UNION ALL SELECT MasterCriteriaRowId, CriteriaID + 1, MaxCriteriaID FROM MasterCriteria WHERE CriteriaID + 1 <= MaxCriteriaID ) SELECT MasterCriteriaRowId, CriteriaID, SomeDetailInformation FROM MasterCriteria left join DetailCriteriaTable on MasterCriteria.CriteriaID = DetailCriteriaTable.DetailCriteriaRowId order by MasterCriteriaRowId, CriteriaID; GO