7 You can use the `PIVOT` function to perform this query. My answer will include both a *static* and a *dynamic* version because sometimes it is easier to understand it using a static version.
5 Although this might be a bit of a co-incidence due to the artificial nature of your test data, being as you mentioned SQL 2012 I tried a rewrite:
2 Your intermittent errors probably stem from the explicit `CAST` conversions that you're performing, combined with the `WHERE` filters. For any given query, SQL Server will create an execution plan, which you can think of as a roadmap of operations in a given order. The execution plan for any query can often vary in a number of ways, and SQL Server tries to choose the plan that will be the most efficient for every particular scenario. In this choice, lots of factors will influence how the plan is generated.
2 You should not make any assumptions about how SQL Server ***will*** process your query, except this: you should always assume that SQL Server ***can*** process your query in a way that is different from how it is explicitly written on the screen. And also this behavior can change based on any of the factors that can influence whether a new plan will be used for the next execution of even the same query, so if you apply a hint or change the query in any way or add or remove an index and the error goes away, don't assume the error won't come back tomorrow.
3 As of writing this, there is no way to insert multiple rows into just an `IDENTITY` column using an `INSERT` statement. The `DEFAULT VALUES` placeholder stands for just one row. And the `INSERT ... SELECT` syntax has no extension to support the same functionality as the `DEFAULT VALUES` clause.