I want to continually make the point that nearly everything I post in this corner of the interwebs can be categorized as “Things I have learned” and not “Things I have made.” Despite my years of work with SQL Server databases, I have used learned concepts from others far more than I have discovered myself. And I’m not ashamed to say this, because for any given task we can either reinvent the proverbial wheel or we can use one of the many wheels in working condition that are already in existence. I would like to think my employers don’t care who made it, so long as it’s a correct, timely, and scalable solution.

Which brings us to Sven. Well, almost.

That being said, I will also endeavor to attribute my acquired knowledge shown here to whatever source is appropriate. I’d like to think I know a thing or two about this SQL stuff, but I wasn’t born knowing it so it would be foolish of me to write as if that were the case. Every day, with every line of T-SQL I write, I am standing on the shoulders of the various giants who have gone before me and shared something with me, whether by personal or mass communications.

Which now brings us to Sven. OK, not quite yet. Soon.

Early last week I was asked by one of our developers if there was a way to find the minimum value for a record across multiple columns. As most of you know, MIN and other aggregate functions work vertically, which means they read the data in a single column* across multiple records. What we were discussion was the opposite, and he was trying to use a PIVOT function and was getting all flustered and frustrated. Flustrated.

(*It is possible to aggregate for multiple columns vertically, but that’s a different post for a different day.)

Anyhow, I recall a solution but I couldn’t remember the exact syntax, so after like 60 seconds of furious search engine abuse I found the Stack Overflow post with good old Sven and his 660+ up votes. It’s an informative thread, so I recommend you read it.

In case you don’t feel like clicking through, shame on you. In case you have no shame, the solution is to affectively create a subquery with VALUES of data from the columns after the data has been read, which helps minimize your data reads. And minimal data reads is a good thing.

Here’s a fun version you can try, where the low value is cleverly obvious by being the same as the record ID.

CREATE TABLE #Test (
       ID int
       , Col1 int
       , Col2 int
       , Col3 int
       , Col4 int
       , Col5 int
       )
 
INSERT #Test VALUES (1, 1, 2, 3, 4, 5)
INSERT #Test VALUES (2, 100, 2, 300, 400, 500)
INSERT #Test VALUES (3, 10, 20, 3, 40, 50)
INSERT #Test VALUES (4, 11, 21, 31, 4, 51)
INSERT #Test VALUES (5, 19, 29, 39, 49, 5)
 
SELECT
       ID
       ,(
                 SELECT MIN(Col)
                 FROM (VALUES (Col1), (Col2), (Col3), (Col4), (Col5)) AS v (Col)
                 ) AS MinCol
FROM #Test
 
DROP TABLE #Test

Important Note: This only works if your SQL Server version is at least 2008

Semi-Important Note: If you’re querying millions of records, you probably want to have a covering index for these columns

Really Fun Note: You can actually use this solution with any Aggregate Function like SUM, AVG, STDDEV, etc. Just remember that if you use COUNT it COUNTS any NULL values, whereas the others functions do not. But then again, I have no idea why you would use COUNT in this situation.

Now go forth and stand on the shoulders of giants for yourself. And be sure to give Sven an up vote.