-- create CREATE TABLE MasterCriteriaTable ( MasterCriteriaRowID varchar(10), DetailCriteriaID varchar(100) ); -- insert INSERT INTO MasterCriteriaTable VALUES ('1','1,2,3,5-8,13'), ('2','5,6,12,17-20,22-24'); -- fetch SELECT * FROM MasterCriteriaTable; 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 FROM MasterCriteria order by MasterCriteriaRowId, CriteriaID; GO