Q:

UNION ALL vs UNPIVOT to change column values into rows

Why would one want to use one method over the other for taking several column values and moving them into rows with a label classifier and a value column?

A:

UNPIVOT is better from a performance perspective because it only scans the rows once.  UNION ALL is going to scan the rows once for every subquery.  In theory, this doesn't have to happen, but I don't know of an optimizer that would only do one scan.
This is particularly important for large tables or if the "table" is really a complex SQL expression or view.

