PIVOT data type conversion error
Alias the columns in the select. The values in the In (...) part of the Pivot need to be the exact field values observed in the data.

Select CompanyName, [1] As Mon, [2] As Tues, [3] As Wed...
  From Pivot234
  Pivot (Avg(Freight) For DayNumber In ([1], [2], [3], [4], [5], [6], [7])) As p;



  Can you explain why it is necessary to alias the spreading columns? Why can I not just simply state the columns as I did in the PIVOT table operator and in the SELECT statement?


The Pivot(Sum([ValueField]) For [CategoryField] In ('Category A', 'Category B' ... )) syntax is looking for the literal values that you supply in the In () to be in the [CategoryField]. It isn't mapping number 1 to position 1, it is searching the field for those exact values.