A problem which was posted on a forum was how to select the highest valued column from a row, given a specific set up.
The design of the table was like this:
And content looked something like this:
Being personally interested in SQL I thought this was a fun little problem to test out myself, so I gave it a shot. Now the way I would have done this in the old day and the “easiest to think up” solution (in my opinion) was to simply case my way through it.
Making something like this:
SELECT PersonName,
CASE
WHEN Score1 < Score2 THEN
CASE
WHEN Score2 < Score3 THEN Score3 --Score3 is highest
ELSE Score2 --Score2 is highest
END
ELSE
CASE
WHEN Score1 < Score3 THEN Score3 --Score3 is highest
ELSE Score1 --Score1 is higest
END
END AS HighScore
FROM Score
GO
Simple enough and it returns:
However, because I’m working with SQL Server 2005 and have started to explore the syntax possibilities of that one, and – well because the shown CASE syntax isn’t exactly user friendly to read, for people to maintain, or even worse, if a Score4 is suddenly introduced. The complexity in it can quickly become unsurmountable.
So I thought about it a bit and I recalled the PIVOT syntax that it could flip table rows into columns, and the UNPIVOT which could flip columns into rows. This makes UNPIVOT fit this problem exactly.
So I started to build a query using UNPIVOT:
SELECT PersonName, UnPivotColumnName, UnPivotScore
FROM
(
SELECT PersonName, Score1, Score2, Score3
FROM Score) T1
UNPIVOT
(
UnPivotScore FOR UnPivotColumnName IN (Score1, Score2, Score3)
) T2
GO
Which gave me the result like this:
Now that is halfway there, now I only needed to group on the Name and select the higest score. For this I decided to use the Common Table Expression syntax instead of making a tempoary table … simply because I like CTE’s :)
That resulted in this query:
WITH UnpivotCTE AS (
SELECT PersonName, UnPivotColumnName, UnPivotScore
FROM
(
SELECT PersonName, Score1, Score2, Score3
FROM Score) T1
UNPIVOT
(
UnPivotScore FOR UnPivotColumnName IN (Score1, Score2, Score3)
) T2
)
SELECT PersonName, MAX(UnPivotScore) AS HighScore
FROM UnpivotCTE
GROUP BY PersonName
GO
Which resulted in the same result as my “CASE” syntax.
I then quickly – just for fun – ran an estimated query plan for both, and the CASE was significantly faster, but I would argue that the manageability of the latter (UNPIVOT) is vastly improved, because as I mentioned ealier – imagine if we needed to add a column Score4 or perhaps a Score5, 6 and 7 as well. Imagine how the CASE would look then, whereas I could simply expand on the UNPIVOT syntax easily.