In the Article, Pivot Rolling Periods Without Using Dynamic T-SQL (experts-exchange.com/viewArticle.jsp?aid=654), I explored the pivot of moving date based reports without the use of dynamic T-SQL, but still allow for re-use month after month without re-writing of code. 

Here is another introductory tidbit to SQL Server 2005's PIVOT (technet.microsoft.com/en-us/library/ms177410.aspx).  We will embrace the limitations of the PIVOT keyword requiring hard coded values / column names, but still provide nice reports that can be used in views or quick queries without getting into multi-line dynamic code.  

To play along, you can download and run the following DDL and data insert SQL script:

5826.bookings-data.sql

Okay, now we can play.  The following starts out by getting data greater or equal to last January 1st at midnight "dateadd(year, datediff(year, 0, getdate())-1, 0)".  Then we employ a date function to make our datetime value non-specific to year (i.e., month(bk_date) will yield 1-12).  For this report, we want to have a year over year analysis of data by month so we also use year(bk_date) to get our grouped column heading which doesn't affect us in the PIVOT since it will remain as data.

;with bks( byear, bmonth, amount )
as
(
select year(bk_date), month(bk_date), bk_amount
from bookings
where bk_date >= dateadd(year, datediff(year, 0, getdate())-1, 0)
)
select byear as "Sales Year",
coalesce("1", 0.00) as January,
coalesce("2", 0.00) as February,
coalesce("3", 0.00) as March,
coalesce("4", 0.00) as April,
coalesce("5", 0.00) as May,
coalesce("6", 0.00) as June,
coalesce("7", 0.00) as July,
coalesce("8", 0.00) as August,
coalesce("9", 0.00) as September,
coalesce("10", 0.00) as October,
coalesce("11", 0.00) as November,
coalesce("12", 0.00) as December
from bks
pivot( sum(amount) for bmonth 
       in ("1","2","3","4","5","6","7","8","9","10","11","12") ) pvt
order by "Sales Year" desc

Results will look similar to below:

The above uses the month number, since it has to be typed multiple times as use explicit select list to use functions like coalesce().  This is more important when doing advanced pivoting like with multiple columns (experts-exchange.com/viewArticle.jsp?aid=1537); however, since we have a simple case here where null may be okay as it quickly answers the question of a missing data point versus a very poor $0.00 booking month, we can use a much simplified code:

with bks("Sales Year", bmonthname, amount)
as
(
select year(bk_date), datename(month, bk_date), bk_amount
from bookings
where bk_date >= dateadd(year, datediff(year, 0, getdate())-1, 0)
)
select *
from bks
pivot( sum(amount) for bmonthname
       in (January, February, March, April, May, June,
           July, August, September, October, November, December) ) pvt
order by "Sales Year" desc

This version looks similar to this:

As you will see, we haven't lost any value in the actual data and gained less code to maintain and a clear cut distinction between 0's and no data as already stated.  We utilize the datename() function here to strip out the year and day as we did before, but instead of just a number we return full month.  For consistency, you can use left( datename( month, bk_date ), 3 ) and you will get 'Jan', 'Feb', 'Mar', etc.  It is normally not a good practice to use wilcard (*) for selecting records, but since we explicitly defined the columns we wanted in the CTE, 'select *' is very safe here as we are still protected from changes in the underlying table structure as we are selecting all from the CTE and not the base table. 

Notice, we still following the same syntax on the pivot, just changed the actual values in play.

pivot( aggregate_function( column_to_aggregate ) for column_to_pivot
       in ( pivot_value1 [, pivot_value2 [, ... pivot_valuen]] )
) pivot_alias

Hopefully that was helpful!

This was my first entry, so hopefully you liked it.  Will work on adding more to it if folks are interested as I use these kinds of queries frequently for things like item inventory turns over or dependent / independent usage over the past 12 months, so have played with it a bit.

Happy coding!

-- Kevin