By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
create table tableA (id int identity,AttributeA varchar(10),AttributeB varchar(10),AttributeC varchar(10));
insert into tableA
SELECT 'A' AS AttributeA, 'B' AS AttributeB, 2020 AS AttributeC
UNION ALL
SELECT 'A' AS AttributeA, 'B' AS AttributeB, 2021 AS AttributeC
UNION ALL
SELECT 'B' AS AttributeA, 'C' AS AttributeB, 2022 AS AttributeC;
select * from tableA;
create table conditions (id int identity, ConditionA varchar(100), ConditionB varchar(100)
, ConditionC varchar(100), Grade varchar(100));
insert into conditions
SELECT 'IN (''A'')' AS ConditionA, 'IN (''B'')' AS ConditionB, '< 2021' AS ConditionC, 'Bad' AS Grade
UNION ALL
SELECT 'IN (''A'')' AS ConditionA, 'IN (''B'')' AS ConditionB, 'IN (2021)' AS ConditionC, 'Good' AS Grade
UNION ALL
SELECT 'NOT IN (''A'')' AS ConditionA, 'IN (''C'')' AS ConditionB, '> 2021' AS ConditionC, 'Excellent' AS Grade
;
select * from conditions;
| id | AttributeA | AttributeB | AttributeC |
|---|---|---|---|
| 1 | A | B | 2020 |
| 2 | A | B | 2021 |
| 3 | B | C | 2022 |
| id | ConditionA | ConditionB | ConditionC | Grade |
|---|---|---|---|---|
| 1 | IN ('A') | IN ('B') | < 2021 | Bad |
| 2 | IN ('A') | IN ('B') | IN (2021) | Good |
| 3 | NOT IN ('A') | IN ('C') | > 2021 | Excellent |
select *
,concat('(',Id
,',case when '
,'AttributeA ',ConditionA
,' and ','AttributeB ',ConditionB
,' and ','AttributeC ',ConditionC
,' then ''', Grade,''' end)') cond
from conditions
| id | ConditionA | ConditionB | ConditionC | Grade | cond |
|---|---|---|---|---|---|
| 1 | IN ('A') | IN ('B') | < 2021 | Bad | (1,case when AttributeA IN ('A') and AttributeB IN ('B') and AttributeC < 2021 then 'Bad' end) |
| 2 | IN ('A') | IN ('B') | IN (2021) | Good | (2,case when AttributeA IN ('A') and AttributeB IN ('B') and AttributeC IN (2021) then 'Good' end) |
| 3 | NOT IN ('A') | IN ('C') | > 2021 | Excellent | (3,case when AttributeA NOT IN ('A') and AttributeB IN ('C') and AttributeC > 2021 then 'Excellent' end) |
select string_agg(
concat('(',Id,',''AttributeA ',ConditionA,' and ','AttributeB ',ConditionB,' and ','AttributeC ',ConditionC,')')
,',')cond
from conditions
| cond |
|---|
| (1,'AttributeA IN ('A') and AttributeB IN ('B') and AttributeC < 2021),(2,'AttributeA IN ('A') and AttributeB IN ('B') and AttributeC IN (2021)),(3,'AttributeA NOT IN ('A') and AttributeB IN ('C') and AttributeC > 2021) |
select *
from tableA a
cross apply( values
(1,case when AttributeA IN ('A') and AttributeB IN ('B') and AttributeC< 2021 then 'Bad' end)
,(2,case when AttributeA IN ('A') and AttributeB IN ('B') and AttributeC IN (2021) then 'Good' end)
,(3,case when AttributeA NOT IN ('A') and AttributeB IN ('C') and AttributeC > 2021 then 'Excellent' end)
)c(ConditionId,res)
where res is not null
| id | AttributeA | AttributeB | AttributeC | ConditionId | res |
|---|---|---|---|---|---|
| 1 | A | B | 2020 | 1 | Bad |
| 2 | A | B | 2021 | 2 | Good |
| 3 | B | C | 2022 | 3 | Excellent |
select * from tableA a
cross apply( values
(1,AttributeA IN ('A') and AttributeB IN ('B') and AttributeC < 2021)
,(2,AttributeA IN ('A') and AttributeB IN ('B') and AttributeC IN (2021))
,(3,AttributeA NOT IN ('A') and AttributeB IN ('C') and AttributeC > 2021)
)c(ConditionId,res)
where res is not null
Msg 156 Level 15 State 1 Line 3
Incorrect syntax near the keyword 'IN'.
declare @sqlExpr varchar(1000);
set @sqlExpr=
'select *
from tableA a
cross apply( values '+CHAR(13)
+(select string_agg(
concat(' (',Id,',','case when ','AttributeA ',ConditionA,' and ','AttributeB ',ConditionB,' and ','AttributeC ',ConditionC,' then ''',Grade,''' end) ')
,','+ CHAR(13) )cond
from conditions
)
+'
)c(ConditionId,res)
where res is not null'
;
select @sqlExpr;
exec (@sqlExpr);
| (No column name) |
|---|
| select * from tableA a cross apply( values (1,case when AttributeA IN ('A') and AttributeB IN ('B') and AttributeC < 2021 then 'Bad' end) , (2,case when AttributeA IN ('A') and AttributeB IN ('B') and AttributeC IN (2021) then 'Good' end) , (3,case when AttributeA NOT IN ('A') and AttributeB IN ('C') and AttributeC > 2021 then 'Excellent' end) )c(ConditionId,res) where res is not null |
| id | AttributeA | AttributeB | AttributeC | ConditionId | res |
|---|---|---|---|---|---|
| 1 | A | B | 2020 | 1 | Bad |
| 2 | A | B | 2021 | 2 | Good |
| 3 | B | C | 2022 | 3 | Excellent |