Why does this query work with a CTE, but not with a derived table?
You are missing an alias for the derived table.

Books online - Emphasis mine


  When a derived table, rowset or table-valued function, or operator clause (such as PIVOT or UNPIVOT) is used, the required table_alias at the end of the clause is the associated table name for all columns, including grouping columns, returned.