Sales by month cross tab query using pivot I am trying to show sales by month using PIVOT in Sql Server 2005.  I know it's a common question, but I just can't figure out what I am missing here:

select ShippedYear, 1 as Jan, 2 as Feb
from
(
    select  datepart(year, ss.SHIPPED_DATE) as ShippedYear
          , ss.SHIPPED_AMOUNT as Amount
          , datepart(month, ss.SHIPPED_DATE)) as MonthCode
    from    dbo.SALES_SHIPPER_LINE ssl join dbo.SALES_SHIPPER ss on ss.ID = ssl.SHIPPER_ID
) ps
pivot(
    sum(amt) for MonthCode in (1,2,3,4,5,6,7,8,9,10,11,12) --this shows error??
) piv;


I am getting an error at the "in" clause in my pivot.  Can anyone tell me what I am doing wrong?
Since your new columns are numbers, you need to escape the names with square brackets:

select ShippedYear, 1 as Jan, 2 as Feb
from
(
    select  datepart(year, ss.SHIPPED_DATE) as ShippedYear
          , ss.SHIPPED_AMOUNT as Amount
          , datepart(month, ss.SHIPPED_DATE)) as MonthCode
    from    dbo.SALES_SHIPPER_LINE ssl join dbo.SALES_SHIPPER ss on ss.ID = ssl.SHIPPER_ID
) ps
pivot(
    sum(amt) for MonthCode in ([1],[2],[3],[4],[5],[6],[7],[8],[9],[10],[11],[12])
) piv;


The square brackets [] are used to delimit identifiers. Since the column name begins with a number, this must be delimited. See MSDN for more details.