You could take a more dynamic approach and cross join your locations with your months to get all possible combinations of location / month, and then left join your original table to the results - you could then use the ISNULL function in the pivot source to deal with NULL values - for example:

CREATE TABLE #office_info_by_location_id

(

location_name VARCHAR(50) NULL,

month VARCHAR(7) NULL,

total_offices INT NULL

);

INSERT INTO #office_info_by_location_id

VALUES ('a', '2008-03', 3);

INSERT INTO #office_info_by_location_id

VALUES ('a', '2008-04', 4);

INSERT INTO #office_info_by_location_id

VALUES ('b', '2008-03', 5);

SELECT x.[month],x.location_name,ISNULL(y.total_offices,0) AS 'total_offices'

INTO #PivotSource

FROM

(

SELECT DISTINCT a.[month],b.location_name

from #office_info_by_location_id a

CROSS JOIN

#office_info_by_location_id b

) x

LEFT JOIN

(

SELECT location_name,[month],total_offices

FROM #office_info_by_location_id

) y ON x.location_name = y.location_name and x.[month] = y.[month]

--now pivot the results using dynamic SQL

DECLARE @cols AS NVARCHAR(MAX);

DECLARE @query AS NVARCHAR(MAX);

SELECT @cols = STUFF(( SELECT DISTINCT TOP 100 PERCENT

'],[' + t2.month

FROM #PivotSource AS t2

ORDER BY '],[' + t2.month

FOR XML PATH('')

), 1, 2, '') + ']'

SELECT @query =

'SELECT *

FROM

#PivotSource AS t

PIVOT

(

MAX(total_offices)

FOR [month] IN( ' + @cols + ' )' +

' ) AS p ; ';

EXECUTE(@query);