How to get the max two values from each row and concatenate them in SQL?

Question

I have created Table 1 for vehicles, and it has color values (Red, Blue, Yellow). I have to update Table 2's Colors field by getting each row's max two values field names of Table 1 and concatenate them.

By using a nested if-else, I could get the first max value, but could not get the second max values of each row.

Table 2 field Color has the expected update result:

TABLE 1

Vehicle  Red   Blue  Yellow
Honda    10     20    25
Yamaha   12     10    22
Suzuki   25     30    40

TABLE 2

Vehicle   Color
Honda     Blue/Yellow
Yamaha    Red/Yellow 
Suzuki    Blue/Yellow

Tag the DBMS (i.e. `MySQL`, `SQL Server`, etc..) that you are using. — Yogesh Sharma, Jun 11 '18 at 14:14
I'm using SQL server ---------- version details Microsoft SQL Server 2014 - 12.0.4100.1 (X64) Apr 20 2015 17:29:27 Copyright (c) Microsoft Corporation Standard Edition (64-bit) on Windows NT 6.3 (Build 9600: ) (Hypervisor) — Vasanth, Jun 11 '18 at 14:18
This may help with some guidance - https://stackoverflow.com/a/71045/1313067 . — Hatt, Jun 11 '18 at 14:24

Yogesh Sharma · Answer 1 · 2018-06-11T14:34:03.787

You can use cte :

with t as (
        select *, row_number() over (partition by Vehicle order by ColrVal) Seq
        from table1 t1 cross apply (
              values ('Red', Red), ('Bule', Blue), ('Yellow', Yellow) 
             ) t2 (Colr, ColrVal)
)

select Vehicle,
       stuff ( (select '/'+Colr 
                from t t2
                where t2.Vehicle = t1.Vehicle and t2.Seq > 1
                for xml path('')
                ), 1, 1, ''
             ) as Color
from t t1
group by Vehicle;

However, this uses the row_number() function to get the top most colors and doing the group_concat using xml method with stuff() function.

Gordon Linoff · Accepted Answer · 2018-06-11T15:05:29.363

With just three colors -- assuming all values are unique -- you can get by with case expressions:

select ((case when red > blue and red > yellow then 'red'
              when blue > yellow then 'blue'
              else 'yellow'
          end) + '/' +
         (case when red > blue and red < yellow or red < blue and red > yellow then 'red'
               when blue > red and blue < yellow or blue < red and blue < yellow then 'blue'
               else 'yellow'
          end)
        )

The results are slightly different from your stated results. The dominant color is first -- but that seems like a benefit.

If you want to use cross apply, then one (relatively) simple method is:

select max(c.color) + '/' + min(c.color)
from t cross apply
     (select top (2) v.*
      from (values ('red', red), ('blue', blue), ('yellow', yellow)) v(color, val)
      order by val desc
     ) c;

The colors are not ordered by their value. You can easily arrange that:

select max(case when seqnum = 1 then c.color en) + '/' + max(case when seqnum = 1 then c.color end)
from t cross apply
     (select v.*, row_number() over (order by v.val desc) as seqnum
      from (values ('red', red), ('blue', blue), ('yellow', yellow)) v(color, val)
     ) c;

How to get the max two values from each row and concatenate them in SQL?

2 Answers2