December 13, 2011 at 8:28 am
I'm trying to write a stored procedure using a pivot table and I can't seem to wrap my head around it. Any pointers are appreciated.
Here's a sample of my data structure. The "1" is a Yes response and the "0" is a No response.
Category Month Y/N
Cat A October 1
Cat A October 0
Cat A October 1
Cat A November 1
Cat B October 0
Cat B November 1
I want the results to be like this (3 month timeframe). The number are the counts of the Yes(1) and No(0) for each category by month.
Category Month1 TotYes TotNo Month2 TotYes TotNo Month3 TotYes TotNo
Cat A October 2 1 November 1 0 December 0 0
Cat B October 0 1 November 1 0 December 0 0
This is my query so far:
Select *
from
(select Page,
DateName(month, surveydate) as MthName, count(num_results) as TotalYes
from tbl_PublicUsefulSurvey
where surveydate > dateadd(month, -3, getdate())
and num_results = 1
group by page, DateName(month, surveydate), num_results) p
PIVOT
(Count(Page)
for MthName
in ([Month1], [Month2], [Month3])
)
as pvt
December 13, 2011 at 8:33 am
Please post DDL and sample data create scripts and it'll be easier to help you.
______________________________________________________________________
Personal Motto: Why push the envelope when you can just open it?
If you follow the direction given HERE[/url] you'll likely increase the number and quality of responses you get to your question.
Jason L. SelburgDecember 13, 2011 at 8:44 am
The users are asked a question and their category, the date they answer, and whether they respond with a Yes or No answer is recorded.
create table PublicSurvey (Category varchar(10), DateEntered DateTime, Answer int)
Insert into PublicSurvey (A, '10/12/2011', 1)
Insert into PublicSurvey (A, '10/13/2011', 0)
Insert into PublicSurvey (A, '10/14/2011', 1)
Insert into PublicSurvey (A, '11/12/2011', 1)
Insert into PublicSurvey (B, '10/17/2011', 0)
Insert into PublicSurvey (B, '11/21/2011', 1)
December 13, 2011 at 8:51 am
This doesn't use pivot but should do the job anyway:
SET DATEFORMAT YMD
;WITH data as
(
SELECT 'Cat A' as category, '2011-10-01' as date,1 as response
UNION ALL SELECT 'Cat A','2011-10-01',0
UNION ALL SELECT 'Cat A','2011-10-01',1
UNION ALL SELECT 'Cat A','2011-11-01',1
UNION ALL SELECT 'Cat B','2011-10-01',0
UNION ALL SELECT 'Cat B','2011-11-01',1
)
SELECT
Category
,SUM(CASE WHEN response = 1 AND MONTH(getdate()) - MONTH(date) = 2 THEN 1 ELSE 0 END) AS month1_yes
,SUM(CASE WHEN response = 0 AND MONTH(getdate()) - MONTH(date) = 2 THEN 1 ELSE 0 END) AS month1_no
,SUM(CASE WHEN response = 1 AND MONTH(getdate()) - MONTH(date) = 1 THEN 1 ELSE 0 END) AS month2_yes
,SUM(CASE WHEN response = 0 AND MONTH(getdate()) - MONTH(date) = 1 THEN 1 ELSE 0 END) AS month2_no
,SUM(CASE WHEN response = 1 AND MONTH(getdate()) - MONTH(date) = 0 THEN 1 ELSE 0 END) AS month2_yes
,SUM(CASE WHEN response = 0 AND MONTH(getdate()) - MONTH(date) = 0 THEN 1 ELSE 0 END) AS month2_no
FROM data
WHERE date >= DATEADD(MM,DATEDIFF(mm,0,GETDATE()) - 2,0)
GROUP BY category
December 13, 2011 at 8:55 am
Thanks.
December 13, 2011 at 9:22 am
;WITH Preaggregate AS (
SELECT
Category,
[MonthEntered]= DATENAME(month,DateEntered),
[TotYes]= SUM(CASE WHEN Answer = 1 THEN 1 ELSE 0 END),
[TotNo]= SUM(CASE WHEN Answer = 0 THEN 1 ELSE 0 END)
FROM PublicSurvey s
GROUP BY Category, DATENAME(month,DateEntered)
)
SELECT
Category,
Month1 = 'October',
TotYes1 = SUM(CASE WHEN p.MonthEntered = 'October' THEN TotYes ELSE 0 END),
TotNo1 = SUM(CASE WHEN p.MonthEntered = 'October' THEN TotNo ELSE 0 END),
Month2 = 'November',
TotYes2 = SUM(CASE WHEN p.MonthEntered = 'November' THEN TotYes ELSE 0 END),
TotNo2 = SUM(CASE WHEN p.MonthEntered = 'November' THEN TotNo ELSE 0 END),
Month2 = 'December',
TotYes2 = SUM(CASE WHEN p.MonthEntered = 'December' THEN TotYes ELSE 0 END),
TotNo2 = SUM(CASE WHEN p.MonthEntered = 'December' THEN TotNo ELSE 0 END)
FROM (SELECT MonthEntered = 'October' UNION ALL SELECT 'November' UNION ALL SELECT 'December') m
CROSS JOIN Preaggregate p
WHERE p.MonthEntered = m.MonthEntered
GROUP BY p.Category
For better assistance in answering your questions, please read this[/url].
Hidden RBAR: Triangular Joins[/url] / The "Numbers" or "Tally" Table: What it is and how it replaces a loop[/url] Jeff Moden[/url]
December 13, 2011 at 10:48 am
Thanks Chris!
December 14, 2011 at 2:53 pm
My boss forgot to tell me he wants to be able to select a date range for this. Any idea how to do this without knowing how many months will be involved until the user selects the date range?
December 14, 2011 at 3:10 pm
I believe you need dynamic SQL for this.
Search this site or the web using these keywords:
dynamic sql pivot
dynamic sql crosstab
December 14, 2011 at 7:30 pm
bill.sutcliffe (12/14/2011)
My boss forgot to tell me he wants to be able to select a date range for this. Any idea how to do this without knowing how many months will be involved until the user selects the date range?
I sure do... Please see the following article...
http://www.sqlservercentral.com/articles/Crosstab/65048/
--Jeff Moden
Change is inevitable... Change for the better is not.
December 15, 2011 at 5:08 am
Thank you Chrissy and Jeff. I appreciate the quick replies. This is good information. Time to get to work.
December 15, 2011 at 8:02 am
Jeff,
I've read part 1 and 2 of your articles and also the article on the Tally table. It looks like you hard code the months in your SUM statements. I don't know what my months will be since they are using a date range. My query so far looks like this.
Page TotalMonth TotalYes
32ndMEDBDEJuly 3
about_ameddJuly 8
ahs July 7
32ndMEDBDEAugust 5
about_ameddAugust 6
ahs August 11
What I want to see is this.
July August
32ndMEDBDE 3 5
about_amedd 8 6
ahs 7 11
I won't know ahead of time that they want to see July and August. What am I missing?
December 15, 2011 at 8:22 pm
bill.sutcliffe (12/15/2011)
Jeff,I've read part 1 and 2 of your articles and also the article on the Tally table. It looks like you hard code the months in your SUM statements. I won't know ahead of time that they want to see July and August. What am I missing?
What you're missing is the dynamic SQL in the Part 2 article. 🙂
--Jeff Moden
Change is inevitable... Change for the better is not.
December 15, 2011 at 8:24 pm
bill.sutcliffe (12/15/2011)
Jeff,I've read part 1 and 2 of your articles and also the article on the Tally table. It looks like you hard code the months in your SUM statements. I don't know what my months will be since they are using a date range. My query so far looks like this.
Page TotalMonth TotalYes
32ndMEDBDEJuly 3
about_ameddJuly 8
ahs July 7
32ndMEDBDEAugust 5
about_ameddAugust 6
ahs August 11
What I want to see is this.
July August
32ndMEDBDE 3 5
about_amedd 8 6
ahs 7 11
I won't know ahead of time that they want to see July and August. What am I missing?
Bill, do you have some data you can share in a readily consumable format IAW the first link in my signature below?
{EDIT} Gah! If it were a snake, it would have bit me. I'll be back.
--Jeff Moden
Change is inevitable... Change for the better is not.
December 16, 2011 at 10:00 pm
Alright, Bill... here we go!
First, we need a little bit more data than what you provided so we can see this work. A million rows spread out from 2000-01-01 through 2011-12-31 should do. 🙂 This takes about 60 seconds on my 9 year old, single CPU desktop. It should take less than that on your more modern machine.
--=================================================================================================
-- Build the test data. Nothing in this section is a part of the solution.
-- We're just building test data here.
--=================================================================================================
--===== Conditionally drop the test table to make reruns in SSMS easier
IF OBJECT_ID('tempdb..#PublicSurvey','U') IS NOT NULL DROP TABLE #PublicSurvey;
--===== Build and populate the test table on-the-fly.
SELECT TOP 1000000
Category = CAST(CHAR(ABS(CHECKSUM(NEWID()))%26+65) AS VARCHAR(10)),
DateEntered = DATEADD(dd,ABS(CHECKSUM(NEWID()))%DATEDIFF(dd,'2000','2012'),'2000'),
Answer = ABS(CHECKSUM(NEWID()))%2
INTO #PublicSurvey
FROM sys.all_columns ac1
CROSS JOIN sys.all_columns ac2
;
--===== Create the quintessential clustered index for performance reasons
CREATE CLUSTERED INDEX IX_#PublicSurvey_Composite01
ON #PublicSurvey (DateEntered, Category)
;
Up next, you'll need the proverbial Swiss Army Knife (thanks to Gianluca for the nick-name) of T-SQL... a Tally Table. Here's how to build one.
--===================================================================
-- Create a Tally table from 0 to 11000
--===================================================================
--===== Create and populate the Tally table on the fly.
SELECT TOP 11001
IDENTITY(INT,0,1) AS N
INTO dbo.Tally
FROM Master.sys.ALL_Columns ac1
CROSS JOIN Master.sys.ALL_Columns ac2
;
--===== Add a CLUSTERED Primary Key to maximize performance
ALTER TABLE dbo.Tally
ADD CONSTRAINT PK_Tally_N
PRIMARY KEY CLUSTERED (N) WITH FILLFACTOR = 100
;
--===== Allow the general public to use it
GRANT SELECT ON dbo.Tally TO PUBLIC
;
GO
The reason why we need it is to build dates so if your data is missing a month, the month will still be displayed.
Last but not least, the solution to your problem. This could very easily be turned into a stored procedure.
--=================================================================================================
-- Solve the problem
--=================================================================================================
--===== Declare some obviously named variables. The ones that start with "@p" would make good
-- candidates for parameters for a stored procedure.
DECLARE @pStartMonth DATETIME,
@pEndMonth DATETIME,
@sql NVARCHAR(MAX)
;
--===== Define the range of months we want. You're "limited" to 11001 months here because of the
-- size of the Tally Table.
SELECT @pStartMonth = 'Jul 2011',
@pEndMonth = 'Sep 2011'
;
--===== Define the pieces of a "Pre-aggregated Cross Tab" as Dynamic SQL
SELECT @sql = '
WITH
ctePreAgg AS
(
SELECT Category,
MonYear = DATEADD(mm,DATEDIFF(mm,0,DateEntered),0),
TotalYes = SUM(Answer)
FROM #PublicSurvey
WHERE DateEntered >= '+QUOTENAME(@pStartMonth,'''')+'
AND DateEntered < DATEADD(mm,1,'+QUOTENAME(@pEndMonth,'''')+')
GROUP BY Category, DATEDIFF(mm,0,DateEntered)
)
SELECT Category = CASE WHEN GROUPING(Category) = 0 THEN Category ELSE ''Total'' END,
'
+
( --=== This build the months to be selected in the CROSS TAB.
SELECT TOP (DATEDIFF(mm,@pStartMonth,@pEndMonth)+1)
+ SPACE(8)
+ QUOTENAME(RIGHT(CONVERT(NCHAR(11),DATEADD(mm,t.N,@pStartMonth),106),8))
+ ' = SUM(CASE WHEN MonYear = '
+ QUOTENAME(RIGHT(CONVERT(NCHAR(11),DATEADD(mm,t.N,@pStartMonth),106),8),'''')
+ ' THEN TotalYes ELSE 0 END),'
+ CHAR(10)
FROM dbo.Tally t
ORDER BY t.N
FOR XML PATH('')
)
+
' [TotalCategory] = SUM(TotalYes)
FROM ctePreAgg
GROUP BY Category WITH ROLLUP
;
'
--===== Then, run the SQL
EXEC (@SQL);
Change the values for @StartMonth and @EndMonth and see what happens.
--Jeff Moden
Change is inevitable... Change for the better is not.
Viewing 15 posts - 1 through 15 (of 15 total)
You must be logged in to reply to this topic. Login to reply