PIVOT in SQL Server is a virtual cripple compared to what it is in MS Access. It's also usually slower than some traditional methods. See the first article that MickyT listed for more on that.

Here's some test data. You should always do that when asking a question so people will respond more quickly and you'll get tested answers. Se the first link in my signature line below for more info on that.

SELECT TOP 1000000

City = 'City' + RIGHT('00'+ CAST(ABS(CHECKSUM(NEWID()))%100+1 AS VARCHAR(10)),3),

Delivery_Type = (SELECT CASE N WHEN 0 THEN 'Standard' WHEN 1 THEN '2 Day' ELSE 'OverNight' END FROM (SELECT N = ABS(CHECKSUM(NEWID()))%3)d),

Date_Delivered = DATEADD(dd,ABS(CHECKSUM(NEWID()))%DATEDIFF(dd,'Jan 2010','Mar 2013'),'Jan 2010')

INTO #TestData

FROM master.sys.all_columns ac1

CROSS JOIN master.sys.all_columns ac2

;

Here's one solution.

WITH

ctePreAgg AS

( --=== Preaggregate the data for an improvement in performance

SELECT Year = DATEPART(yy,Date_Delivered),

Month = DATEPART(mm,Date_Delivered),

City,

Deliver = Delivery_Type,

MonthCount = COUNT(*)

FROM #TestData--dbo.YourTable --<---<<<< LOOK! You'll need to change this!

GROUP BY DATEPART(yy,Date_Delivered),DATEPART(mm,Date_Delivered),City,Delivery_Type

) --=== Now, pivot the data using a high performance crosstab.

SELECT City,

Year,

Deliver,

[Jan] = SUM(CASE WHEN Month = 1 THEN MonthCount ELSE 0 END),

[Feb] = SUM(CASE WHEN Month = 2 THEN MonthCount ELSE 0 END),

[Mar] = SUM(CASE WHEN Month = 3 THEN MonthCount ELSE 0 END),

[Apr] = SUM(CASE WHEN Month = 4 THEN MonthCount ELSE 0 END),

[May] = SUM(CASE WHEN Month = 5 THEN MonthCount ELSE 0 END),

[Jun] = SUM(CASE WHEN Month = 6 THEN MonthCount ELSE 0 END),

[Jul] = SUM(CASE WHEN Month = 7 THEN MonthCount ELSE 0 END),

[Aug] = SUM(CASE WHEN Month = 8 THEN MonthCount ELSE 0 END),

[Sep] = SUM(CASE WHEN Month = 9 THEN MonthCount ELSE 0 END),

[Oct] = SUM(CASE WHEN Month = 10 THEN MonthCount ELSE 0 END),

[Nov] = SUM(CASE WHEN Month = 11 THEN MonthCount ELSE 0 END),

[Dec] = SUM(CASE WHEN Month = 12 THEN MonthCount ELSE 0 END),

[YearTotal] = SUM(MonthCount)

FROM ctePreAgg

GROUP BY City, Year, Deliver

ORDER BY City, Year, Deliver

;

I added a YEAR column and a YearTotal column. Change as you wish.