By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
create table t (region varchar(20), variable varchar(10), audi varchar(10), porsche varchar(10))
insert into t select 'Australia', 'Orders' , '5000' , '20';
insert into t select 'Australia', 'Price' , '50000', '100000';
insert into t select 'Australia', 'Color' , 'red' , 'green';
insert into t select 'France' , 'Orders' , '99' , '100';
insert into t select 'France' , 'Price' , '50000', '100000';
insert into t select 'France' , 'Color' , 'white', 'blue';
6 rows affected
with x as (
select *,
Max(case when variable='orders' and Try_Convert(int,audi)>=100 then 1 else 0 end) over(partition by region) showAudi,
Max(case when variable='orders' and Try_Convert(int,porsche)>=100 then 1 else 0 end) over(partition by region) showPorsche
from t
)
select Region, Variable,
case when variable='Orders' or ShowAudi=1 then Audi else '0' end Audi,
case when variable='Orders' or ShowPorsche=1 then Porsche else '0' end Porsche
from x;
| Region | Variable | Audi | Porsche |
|---|---|---|---|
| Australia | Orders | 5000 | 20 |
| Australia | Price | 50000 | 0 |
| Australia | Color | red | 0 |
| France | Orders | 99 | 100 |
| France | Price | 0 | 100000 |
| France | Color | 0 | blue |