Finding columns with highest value among other columns in SQL

Question

I have a table that looks like this:

table,
th,
td {
  border: 1px solid black;

<table>
  <tr>
    <th>Customer</th>
    <th>Category 1</th>
    <th>Category 2</th>
    <th>Category 3</th>
    <th>Category 4</th>
  </tr>
  <tr>
    <td>[email protected]</td>
    <td>0</td>
    <td>563</td>
    <td>0</td>
    <td>0</td>
  </tr>
  <tr>
    <td>[email protected]</td>
    <td>33</td>
    <td>31</td>
    <td>38</td>
    <td>13</td>
  </tr>
  <tr>
    <td>[email protected]</td>
    <td>108</td>
    <td>0</td>
    <td>0</td>
    <td>0</td>
  </tr>
  <tr>
    <td>[email protected]</td>
    <td>0</td>
    <td>7</td>
    <td>0</td>
    <td>11</td>
  </tr>
</table>

I am trying to insert a new column named "BestCategory" that will show the name of the category that has the highest value in between them.

I have tried to use GREATEST but it's not accepted in my system.

Can you guys help me?

Answer 1

First you have to use UNPIVOT to calculate the maxValue for each row

Then use a CASE to select what is the BestCategory.

Sql Fiddle Demo

WITH maxValues as 
(
   select
     [Customer], Max(Amount) as TheMax
   from 
     Customer 
     UNPIVOT (Amount for AmountCol in 
              ([Category 1], [Category 2], [Category 3], [Category 4])) as unpvt
   group by [Customer]
)        
select 
    Customer.[Customer], [Category 1], [Category 2], [Category 3], [Category 4], 
    TheMax,
    Case 
        WHEN [Category 1] = TheMax THEN '[Category 1]'
        WHEN [Category 2] = TheMax THEN '[Category 2]'
        WHEN [Category 3] = TheMax THEN '[Category 3]'
        ELSE '[Category 4]'
    END  as BestCategory        
from Customer        
inner join maxValues
   on Customer.[Customer] = maxValues.[Customer]

OUTPUT

|      Customer | Category 1 | Category 2 | Category 3 | Category 4 | TheMax | BestCategory |
|---------------|------------|------------|------------|------------|--------|--------------|
| [email protected] |          0 |        563 |          0 |          0 |    563 | [Category 2] |
|  [email protected] |         33 |         31 |         38 |         13 |     38 | [Category 3] |
|  [email protected] |        108 |          0 |          0 |          0 |    108 | [Category 1] |
|  [email protected] |          0 |          7 |          0 |         11 |     11 | [Category 4] |