Transform column data based on row entries-CodePudding

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.

CodePudding user response：

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 z.Audi < 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 z.Porsche < 100) THEN 0 ELSE Porsche END AS Porsche
  FROM
    t a

You can also form a sub-query of those with Audi or Porsche that are low and left join it in. If the join succeeds, audi/porsche is low. If the join fails, it's higher:

  SELECT 
    a.Region,
    a.Variable,
    CASE WHEN Variable <> 'Orders' AND lows.Audi IS NOT NULL THEN 0 ELSE Audi END AS Audi,
    CASE WHEN Variable <> 'Orders' AND lows.Porsche IS NOT NULL THEN 0 ELSE Porsche END AS Porsche
  FROM
    t a
    LEFT JOIN (SELECT * FROM t WHERE Variable = 'Orders' AND (Audi < 100 OR Porsche < 100)) lows

CodePudding user response：

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