CODEWITHSUNDEEP
sqlsql-servert-sql
ruedi
ruedi

Reputation: 5545

Transform column data based on row entries

I have the following table

Region Variable Audi Porsche
Australia Orders 5000 20
Australia Price 50000 100000
Australia Color red green
France Orders 99 100
France Price 50000 100000
France Color white blue

based on the column where Variable Orders < 100 all other entries for this car should be 0.

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

I tried to split the table by Region and used where = but could not get to any result here. I do not even know how it could be accomplished so it would be great if you could help me here.

Upvotes: 0

Views: 64

Answers (2)

Caius Jard
Caius Jard

Reputation: 74595

You could do something like:

  SELECT 
    a.Region,
    a.Variable,
    CASE WHEN a.Variable <> 'Orders' AND EXISTS(SELECT null FROM t z WHERE a.Region = z.Region AND 'Orders' = z.Variable AND TRY_PARSE(z.Audi AS INT) < 100) THEN '0' ELSE Audi END AS Audi,
    CASE WHEN a.Variable <> 'Orders' AND EXISTS(SELECT null FROM t z WHERE a.Region = z.Region AND 'Orders' = z.Variable AND TRY_PARSE(z.Porsche as INT) < 100) THEN '0' ELSE Porsche END AS Porsche
  FROM
    t a

You can also form a sub-query of just those Orders rows and join it in then you have a constant comparable on each row:

SELECT 
    a.Region,
    a.Variable,
    CASE WHEN a.Variable <> 'Orders' AND ords.Audi < 100 THEN '0' ELSE a.Audi END AS Audi,
    CASE WHEN a.Variable <> 'Orders' AND ords.Porsche < 100 THEN '0' ELSE a.Porsche END AS Porsche
  FROM
    t a
    INNER JOIN (SELECT * FROM t z WHERE z.Variable = 'Orders') ords ON a.Region = ords.Region

Upvotes: 1

Stu
Stu

Reputation: 32579

You can make use of aggregation here to efficiently generate a boolean true/false flag for each. note this also has to use try_convert since the columns are presumably varchar in a sort of EAV model.

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

Demo Fiddle

Upvotes: 1

Related Questions