TSQL UNPIVOT to find the MIN and MAX across a row of columns

UNPIVOT - Very eloquent indeed...

So, I didn't finish my MERGE blog. I will, give me a day or so and I'll have some examples. While researching something at work, I noticed that I have never (or don't remember) had to fetch the max or min across columns in a table for a row. Scouring the internet, I found nothing but garbage; mostly CASE statements and a few other things which I didn't particularly care for. I even came up with some brilliant UNION ALL inside of a TSQL function that I really didn't like either, so I thought I'd look around some more. Lo and behold, UNPIVOT! Because we all at some time or another have had to work through the mess of mainframe data, from there we seem to find columns where, in the relational world, we like to think rows. UNPIVOT takes the column mess and flips it on end. This little statement replaces a variety of homespun scripts and procedures that I've seen before - stuffing column names into dynamic SQL no less - to flip rows and columns. Below is a function that I wrote, of which I am not particularly proud of by any stretch of the imagination:

CREATE FUNCTION ufn_GetMaxVal (@id INT)
RETURNS INT
AS
BEGIN
       DECLARE @max1 INT    
                     ,@max2 INT
                     ,@max3 INT
                     ,@val int
       
       SELECT @max1=CAST(max1 AS int) 
              ,@max2 = CAST(max2 AS int)
              ,@max3 = CAST(max3 AS int)
              FROM vals 
       WHERE id=@id         
       
        SELECT @val=MAX(x)
       FROM (SELECT @max1 AS x 
                     UNION 
                     SELECT @max2  
                     UNION 
                     SELECT @max3  
                      
              ) q
       RETURN @val
END
go

This is ok, but functions, temp tables, etc. are not the stealthy way to do things in my opinion, which leads me to another option. Lets go through an example of one. Mine is such because I actually want to update attributes in the source table. Setting up the data first: 

USE tempdb
go
DROP TABLE Pvt
GO
CREATE TABLE Pvt
    (id char(1) NOT NULL PRIMARY KEY
    ,val1 int
    ,val2 int
    ,val3 int
    ,val4 int
    ,val5 int
    ,valMax int
    ,valMin int
    )
go

INSERT INTO Pvt 
VALUES ('A', 1,2,5,8,3, NULL, NULL), ('B',4,3,7,4,5,NULL, NULL), ('C', 9,4,4,5,2,NULL, NULL)
GO
SELECT * FROM Pvt

Running the above snippet, you get the this recordset. ID is the primary key, and values (val1 through val5) are the given integers for which we want the maximum and minimum for the key across columns. We will then update valMax and valMin. Notice that for ID='A', we hope to find the max value of 8 and, let's see, looks like 1 for the minimum. I am going to paste the remainder of the script, and then we'll go through all of it and see if we can figure out the syntax.

UPDATE it
SET valMax =  
                (
                SELECT   MAX(TargetValue) AS targetValue
                    FROM(
                        SELECT ID
                            ,COL
                            ,TargetValue
                        FROM
                            (
                                SELECT ID, Val1, Val2, Val3, Val4, Val5
                                FROM pvt
                            ) AS P
                        UNPIVOT
                            (TargetValue FOR COL IN
                                (Val1, Val2, Val3, Val4, Val5)
                            ) AS Unpvt
                        ) AS L
                    WHERE L.id=it.id
                    GROUP BY ID
                )    
                 
    ,valMin =    
                (
                SELECT   MIN(TargetValue) AS targetValue
                    FROM(
                        SELECT ID
                            ,COL
                            ,TargetValue
                        FROM
                            (
                                SELECT ID, Val1, Val2, Val3, Val4, Val5
                                FROM pvt
                            ) AS P
                        UNPIVOT
                            (TargetValue FOR COL IN
                                (Val1, Val2, Val3, Val4, Val5)
                            ) AS Unpvt
                        ) AS L
                    WHERE L.id=it.id
                    GROUP BY ID
                )               
FROM Pvt AS it
GO

We can figure out what this is doing without having to suffer through the well-known obfuscation of what is Books Online by taking this piece by piece. Usually I start with the innermost statement, and then work outwards. So, for the first select statement, it gives us something similar to what we had above except without the NULL columns for MIN and MAX. This is what we'll "flip". Therefore, for each ID we will have the column values (Val1, etc.) represented as a row value, and the actual numerical value in yet another column.

SELECT ID, Val1, Val2, Val3, Val4, Val5
FROM pvt

SELECT ID
        ,COL
        ,TargetValue
    FROM
        (
            SELECT ID, Val1, Val2, Val3, Val4, Val5
            FROM pvt
        ) AS P
    UNPIVOT
        (TargetValue FOR COL IN
            (Val1, Val2, Val3, Val4, Val5)
        ) AS Unpvt

The above does the "flip". Notice that we UNPIVOT what I am calling TargetValue, which is the actual numeric value that I mentioned. FOR COL represents the old column names (Val1, Val2, etc.) IN the set of values in our domain. Below is a partial recordset for your inspection:

The remainder is fairly straightforward - I have a SET statement to update the MIN and MAX column values, and have added a GROUP BY in an 'equi-join' fashion back to the main table. (Without this I would set the MAX value for every row, and likewise MIN).
 

Lee

--------------------------------

SQL 2008 rulz! Too bad only 20% of the SQL Server world is using it.