I was stuck with a problem at work where I had a very complex query pulling data out of some tables, some XML and what not, and a lot of data manipulating in the query to be able to easier populate a DataWarehouse.
I then had to expand this query and pull some extra Boolean information out, which were based on a logical comparison of two strings, which I would normal do with a CASE like CASE string1 = string2 THEN 1.

However because I had a GROUP BY clause, I could not simply do this, because the columns the strings were taken from was not in the group clause and I didn’t want them there.
So I found out that you can actually put an aggregated function around the case using the following syntax:

SELECT 
<GroupByFields>,
MAX(
    CASE 
        WHEN <NonGroupField> =  'some value' THEN 1
        ELSE 0
    END
)
FROM <TABLE>
GROUP BY <GroupByFields> 
 

This saved me from using a common table expression or two more, and kept things simple. In essense this allows you to make conditional SUM and similar using this technique.
Just more evidence that a lot is possible in SQL, and just trying something often reveals interesting results.

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.

I ran into a SQL oddity today in MS SQL 2005 (and likely all prior versions) - or at least I think it's an oddity.
It revolves around case statements and column aliases, and for some reason you can't use column alias in a case statement. At least neither in the select nor order by clause.

For example:

SELECT myColumn AS C
FROM myTable
ORDER BY C 

This works perfetly well.

Now spice it up with a case (for what ever reason)

SELECT myCoulmn AS C
FROM myTable
ORDER BY
 CASE myCondition
 WHEN myValue THEN C
END 

This for some reason fails because it can't find column C......

I've yet to find the actual reason for why this happens, but that looks odd to me, and it is quite limiting at times.