Sometimes I walk alone outside late at night, when the streets are empty of everything but the whisper of lives lived and lost, and my eye is drawn up to the billions of stars over my head, and I have to wonder: is a SUM/COUNT/MAX/MIN over a VALUES statement the most effective way to calculate an aggregate over columns instead of rows?

The answer, by the way, is “Yes”.

For example:

SELECT keycol,
(SELECT MAX(colval) FROM (VALUES(col1val),(col2val),(col3val),(col4val),(col5val), (col6val)) AS D(val)) AS MaxTable
FROM dbo.TableName

That’s pretty beautiful, considering the mess of UNION and CASE statements this would otherwise require.

Advertisements