-- 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