SQL query: MAX(CASE WHEN...

Factual Questions
6

Sorry, I meant to add this but forgot: what this query is trying to do is essentially a pivot operation, taking column values and transposing them into the structure we want. In standard SQL, the only way to do this is using the CASE and GROUP BY clauses, as we have done above. In many mainstream SQL databases, you can do with via non-standard SQL extensions. I’m most familiar with SQL Server, so this is the syntax I’d use:



SELECT 
*
FROM 
	(SELECT
		Users.id as id,
		Users.username as username,
		Users_meta.meta,
		Users_meta.value
	FROM
		Users
	LEFT JOIN Users_meta
		ON Users_meta.[user] = Users.id) users
PIVOT (
	MAX(users.value)
	FOR users.meta IN ([first_name], [last_name], [user_level])
) AS PivotTable

Results:



id          username   first_name last_name  user_level
----------- ---------- ---------- ---------- ----------
2           amurray    Andy       Murray     4
1           bbrown     Bobby      Brown      1
3           mobama     Michelle   Obama      5
4           ttest      Test       Test       6

(4 row(s) affected)

Identical results, as you can see, with more elegant syntax and no CASE functions required. I’ve also found that this approach gives you better performance, but that might well be a quirk of SQL Server. Also, there’s no warning about NULLS being eliminated: the PIVOT function automatically assumes the elimination of NULL values when an aggregate function is used in the PIVOT clause.

More reading on PIVOT and UNPIVOT in SQL Server, if you’re interested: http://technet.microsoft.com/en-us/library/ms177410(v=sql.105).aspx

Similar functionality in Oracle: ORACLE-BASE - PIVOT and UNPIVOT Operators in Oracle Database 11g Release 1