SQL Server 2016

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.

Create Table QryTable (ID [int], Description [nvarchar](255), CSV_Vals [nvarchar](255))
insert into QryTable  (ID,Description, CSV_Vals) VALUES  
(1,'Description of record #1', 'val_1,val_2,val_3,val_4,val_5,val_6'),
(2,'Description of record #2', 'val_1,val_3,val_6,val_9,val_10,val_11'),
(3,'Description of record #3', 'val_2,val_3,val_4,val_15,val_20,val_21');

Select * from QryTable;

Create Table CriterionTbl (ID [int] identity (1,1), CriterionVals [nvarchar](50));
  
insert into CriterionTbl (CriterionVals) VALUES 
  ('val_3'), 
  ('val_20'), 
  ('val_2'), 
--  ('val_A'), 
  ('val_4');

SELECT * FROM CriterionTbl;

select *
from QryTable QT
where not exists (
    select *
    from CriterionTbl CT
    where ',' + QT.CSV_Vals + ',' not like '%,' + CT.CriterionVals + ',%'
)

select *
from QryTable QT
where 1 = all (
    select case when ',' + QT.CSV_Vals + ',' like '%,' + CT.CriterionVals + ',%' then 1 else 0 end
    from CriterionTbl CT
)


ID
Description
CSV_Vals


1
Description of record #1
val_1,val_2,val_3,val_4,val_5,val_6

2
Description of record #2
val_1,val_3,val_6,val_9,val_10,val_11

3
Description of record #3
val_2,val_3,val_4,val_15,val_20,val_21




ID
CriterionVals


1
val_3

2
val_20

3
val_2

4
val_4




ID
Description
CSV_Vals


3
Description of record #3
val_2,val_3,val_4,val_15,val_20,val_21




ID
Description
CSV_Vals


3
Description of record #3
val_2,val_3,val_4,val_15,val_20,val_21


select *
from(
select q.* 
  ,row_number()over(partition by q.id order by q.id) rn
from QryTable q
inner join CriterionTbl c 
  on concat(',',csv_vals,',') like concat('%,',c.criterionvals,',%')
)t
where rn=(select count(*) from CriterionTbl)


ID
Description
CSV_Vals
rn


3
Description of record #3
val_2,val_3,val_4,val_15,val_20,val_21
4


select *
from(
select q.* 
  ,row_number()over(partition by q.id order by q.id) rn
  ,count(*)over(partition by q.id) qty
from QryTable q
inner join CriterionTbl c 
  on concat(',',csv_vals,',') like concat('%,',c.criterionvals,',%')
)t
where qty=(select count(*) from CriterionTbl)
  and rn=1


ID
Description
CSV_Vals
rn
qty


3
Description of record #3
val_2,val_3,val_4,val_15,val_20,val_21
1
4

ID	Description	CSV_Vals
1	Description of record #1	val_1,val_2,val_3,val_4,val_5,val_6
2	Description of record #2	val_1,val_3,val_6,val_9,val_10,val_11
3	Description of record #3	val_2,val_3,val_4,val_15,val_20,val_21