Pivot Query with Count

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
;

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
;

SELECT City,Delivery_type,[Jan],[Feb],[Mar],[Apr],[May],[Jun],[Jul],[Aug],[Sep],[Oct],[Nov],[Dec]
FROM( SELECT City,Delivery_type,Date_Del = CONVERT(CHAR(3),Date_Delivered,107)
FROM #TestData)PVT
PIVOT
(
COUNT(DATE_DEL)
FOR DATE_DEL IN ([Jan],[Feb],[Mar],[Apr],[May],[Jun],[Jul],[Aug],[Sep],[Oct],[Nov],[Dec])
)AS PRT

IF OBJECT_ID('TempDB..#Results') IS NOT NULL
DROP TABLE #Results

CREATE TABLE #Results (District VARCHAR(50), Location varchar(50), [Q1] INT,[Q2] INT, [Q3] INT,[Q4] INT)

insert into #Results
select District, Location,[Q1],[Q2],[Q3],[Q4] from
(
SELECT SUB_DISTRICT, LOCATION_TYPE, Q = DATEPART(QUARTER, REGISTERED_DATE)
FROM Tata.dbo.Tbl_results_tabdel
WHERE (TB_RESULT_TYPE IN (N'DIRECT', N'DIRECTP', N'CULAUR'))
) SRC

Pivot (count(Q)
for Q in ([Q1],[Q2],[Q3],[Q4])
) as PVT

SELECT * FROM #Results

clyde 73016 (3/18/2013)

Hi Guys

Thx for the responses. I have the following data

SUB_DISTRICT ... LOCATION_TYPE ... Date
Paris Inland 2012/1/1
Nigeria Inland 2012/1/2
Brasil Coast 2012/6/3
Paris Inland 2012/2/2
Nigeria Inland 2012/5/5
Brasil Coast 2012/6/3
Texas Inland 2012/12/12
Paris Inland 2012/11/11
Nigeria Mountain 2012/8/8
Nigeria Mountain 2012/10/10
Texas Inland 2012/10/12
Texas Inland 2012/9/12
Texas Inland 2012/1/1

the ultimate result should be

SUB_DISTRICT LOCATION_TYPE Q1 Q2 Q3 Q4
Paris Inland 2 0 0 1
Nigeria Inland 1 1 0 0
Nigeria Mountain 0 0 0 2
Brasil Coast 0 0 1 0
Texas Inland 1 0 0 3

I have tried creating the T-SQL but to no avail. Can you guide me in the right direction pls

IF OBJECT_ID('TempDB..#Results') IS NOT NULL
DROP TABLE #Results

CREATE TABLE #Results (District VARCHAR(50), Location varchar(50), [Q1] INT,[Q2] INT, [Q3] INT,[Q4] INT)

insert into #Results
	select District, Location,[Q1],[Q2],[Q3],[Q4] from
	(
	SELECT        SUB_DISTRICT, LOCATION_TYPE, Q = DATEPART(QUARTER, REGISTERED_DATE)
	FROM            Tata.dbo.Tbl_results_tabdel
	WHERE        (TB_RESULT_TYPE IN (N'DIRECT', N'DIRECTP', N'CULAUR'))	
	) SRC

Pivot (count(Q)
for Q in ([Q1],[Q2],[Q3],[Q4]) 
) as PVT

SELECT * FROM #Results