SQL Find Max of a row

December 6, 2021

I have tried utilising multiple questions to answer the below, however, can’t seem it get my UNPIVOTS or my PIVOTS to work how i want.

I have 10 columns and thousands of rows.
Columns are [BAND 1], [BAND 2], …, [BAND 10].

I am looking to create an 11th column with the max BAND for each row.

A simple solution is to do something like the below for all 10 columns, however, this will not be complete should an 11th band be added – any alternative methods?

SELECT *,
    CASE 
        WHEN [BAND 1] >= [BAND 2] AND [BAND 1] >= [BAND 3] THEN [BAND 1]
        WHEN [BAND 2] >= [BAND 1] AND [BAND 2] >= [BAND 3] THEN [BAND 2]
        WHEN [BAND 3] >= [BAND 1] AND [BAND 3] >= [BAND 2] THEN [BAND 3]
        ELSE NULL
    END AS [MAX BAND]
FROM [my table]

>Solution :

Ideally, it seems, you should be fixing your design, and instead of 10 band columns you should have 2: a BandValue and a BandNumber column and 1 row per needed band. Then this would be a simple MAX with a GROUP BY.

Unfortunately, as you have a denormalised design you need to do this a little differently. One way is with an inline unpivot:

SELECT {Your Columns}, --Don't use *, define the columns you need
       (SELECT MAX(V.Band)
        FROM (VALUES(MT.[BAND 1]),
                    (MT.[BAND 2]),
                    (MT.[BAND 3]),
                    (MT.[BAND 4]),
                    (MT.[BAND 5]),
                    (MT.[BAND 6]),
                    (MT.[BAND 7]),
                    (MT.[BAND 8]),
                    (MT.[BAND 9]),
                    (MT.[BAND 10]))V(BAND)) AS MaxBand
FROM dbo.[My Table] MT;

This assumes that all the band columns have the same data type.