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.
--Jeff Moden
Change is inevitable... Change for the better is not.