CODEWITHSUNDEEP
sqlsql-serverpivotpivot-table
Nikhil Mangire
Nikhil Mangire

Reputation: 407

How to use pivote to find out the max value from a row with three columns, that is max value out of three columns

I have following table named as 'Table',

enter image description here

Where I want result like following table where if you take first row and last three columns I want value to be 56.

enter image description here

I want sql server code for above table 'Table' and result to be second table. Here MaxV-1 and MaxV-2 are dependent on 'Number' column. MaxV-1 is max value out of FirstV, SecondV and ThirdV when Number is equal to 1 and same logic for MaxV-2.

Upvotes: 1

Views: 47

Answers (1)

Gordon Linoff
Gordon Linoff

Reputation: 1269993

One method is an unpivot and conditional aggreation:

select t.model,
       max(case when t.number = 1 then t.pro_code end) as pro_code_1,
       max(case when t.number = 2 then t.pro_code end) as pro_code_2,
       max(case when t.number = 1 then v.v end) as max_val_1,
       max(case when t.number = 2 then v.v end) as max_val_2
from t cross apply
     (select max(v.v) as v
      from (values (t.firstv), (t.secondv), (t.thirdv)) v(v)
     ) v
group by t.model;

Upvotes: 4

Related Questions