July 11, 2008 at 4:20 pm
I've been given the task of rewriting this report and the guy before me used a bunch of ugly virtual tables and loops etc.. and i'm thinking that this would be a situation that i would want to use pivot maybe? I tried modifying the example on MSDN's site but couldn't figure it out. Basically this is how the raw data could be:
Date Value
4/1 4
4/3 6
4/7 9
4/2 1
5/8 4
5/3 2
5/9 6
5/5 2
And the output i'm trying to achieve is:
Month Total
April 20
May 14
I haven't had to do a pivot table since school, so i can't quite remember how to use one and in what situation, so if there's a better way I'm all ears.
Thanks!
July 11, 2008 at 4:38 pm
What you've requested is not a Pivot... it's a "simple" aggregation. Since you've not identified the datatypes involved, I have to make certain assumptions. Please see the link in my signature line below for how to get the best help quicker on future posts... 😉
Here's a solution to meet the requirements of your post...
[font="Courier New"]--===== Create and populate a test table...
-- THIS IS NOT PART OF THE SOLUTION!!!!
DECLARE @TestTable TABLE (Date DATETIME, Value INT)
INSERT INTO @TestTable (Date,Value)
SELECT '4/1/2008','4' UNION ALL
SELECT '4/3/2008','6' UNION ALL
SELECT '4/7/2008','9' UNION ALL
SELECT '4/2/2008','1' UNION ALL
SELECT '5/8/2008','4' UNION ALL
SELECT '5/3/2008','2' UNION ALL
SELECT '5/9/2008','6' UNION ALL
SELECT '5/5/2008','2'
--===== Aggregate totals by month
-- Derived table does the math...
-- Outer query does the formatting
SELECT DATENAME(mm,totals.DateMonth) AS Month,
totals.Total
FROM (--==== Derived table "totals" finds first of each month and totals
SELECT DATEADD(mm,DATEDIFF(mm,0,Date),0) AS DateMonth,
SUM(Value) AS Total
FROM @TestTable
GROUP BY DATEADD(mm,DATEDIFF(mm,0,Date),0)) totals
[/font]
--Jeff Moden
July 11, 2008 at 4:55 pm
Thanks for the help. I'm getting a syntax error i believe by my GROUP BY. The message is:
Msg 102, Level 15, State 1, Line 23
Incorrect syntax near ')'.
Other than the closing brace for the FROM it appears as if you included everything else needed??? Did some of your code get transformed into that smily face?
July 11, 2008 at 5:11 pm
mblack (7/11/2008)
Thanks for the help. I'm getting a syntax error i believe by my GROUP BY. The message is:Msg 102, Level 15, State 1, Line 23
Incorrect syntax near ')'.
Other than the closing brace for the FROM it appears as if you included everything else needed??? Did some of your code get transformed into that smily face?
Yeah... a parenthesis got converted... I'll fix the code... when you see the smiley face disappear in the code, you know it's been repaired... don't forget to refresh the screen in a minute or two...
--Jeff Moden
July 16, 2008 at 8:43 pm
Hi,
Need an urgent help. Kindly direct me to the correct page if it was answered before.
I have the table data as follows:
P8 X1 3804.00
P1 X2 4.54
P8 X2 4.54
Need to get the output like this:
P8 P1
X1 3804.00 0
X2 4.54 4.54
Help much appreciated.
Thank You,
July 16, 2008 at 9:17 pm
The best thing for this is a "Crosstab Report"... and it's easier than the PIVOT operator...
--===== Create and populate a test table
-- THIS IS NOT PART OF THE SOLUTION
CREATE TABLE dbo.JBMTest
(
ColP CHAR(2),
ColX CHAR(2),
Value DECIMAL(12,2)
)
INSERT INTO dbo.JBMTest
SELECT 'P8','X1','3804.00' UNION ALL
SELECT 'P1','X2','4.54' UNION ALL
SELECT 'P8','X2','4.54'
--===== Demonstrate the CrossTab solution
SELECT ColX,
SUM(CASE WHEN ColP = 'P8' THEN Value ELSE 0 END) AS P8,
SUM(CASE WHEN ColP = 'P1' THEN Value ELSE 0 END) AS P1,
SUM(Value) AS Total
FROM dbo.JBMTest
GROUP BY ColX
ORDER BY ColX
For faster answers in the future, please take a look at the link in my signature below and at the demo table in the above code. Posting self building table will entice people to work on your problem because most folks want to test their code before they post it. Thanks...
--Jeff Moden
July 17, 2008 at 1:06 am
Using PIVOT operator. Check MSDN for more help.
July 17, 2008 at 2:21 am
Here I agree with Jeff ( his is also SQL Server MVP), he writes the very professional code how to do it without PIVOT and simply you do not need PIVOT operation!
Jeff's solution for me is accepted! Very amazing!!! :hehe::w00t::hehe:
Dugi
July 17, 2008 at 6:04 am
I'm in the process of writing an article about Crosstabs and Pivots... hope to finish it soon but one of the things I'm finding out in testing is that, except in smaller cases, the Crosstab method beats the Pivot method performance wise and somtimes it's by a good margin.
And thanks for the compliment, Dugi.
--Jeff Moden
July 17, 2008 at 8:23 am
I find Jeffs answer one that works for this example - but I'm wondering how to do something a bit more...
The solution provided used a case statement where the value in ColP was known. What happens if the value isn't known at design time?
What happens if what you were asking for was something very similar in logic - for example analysis of number of posts by any users over a period of time:
User Date Visit Time
tony.sawyer 16th July 14:30
tony.sawyer 17th July 09:10
tony.sawyer 17th July 10:55
jeff.moden 17th July 11:12
tony.sawyer 17th July 15:13
You can use a select statement of
select date,
sum( case when user = 'tony.sawyer' then 1 else 0 end) as [tony.sawyer count],
sum( case when user = 'jeff.moden' then 1 else 0 end) as [jeff.moden count]
from
group by date
to get:
Date tony.sawyer count jeff.moden count
16th July 1 0
17th July 3 1
What happens when another user accesses the site - if my colleague dave accesses the site on the afternoon of the 17th July I'd like the results to show as follows without having to revisit the select statement
Date tony.sawyer count jeff.moden count dave count
16th July 1 0 0
17th July 3 1 1
I can't think of a simple, elegant solution to create a variable number of columns based on data returned without resorting to some meaty dynamic sql to create a temporary table on the fly based on the core data returned and then selecting the date from that to get the output grid - but then again its getting close to the weekend and my brain is beginning to relax 🙂
July 17, 2008 at 12:08 pm
tony.sawyer (7/17/2008)
I find Jeffs answer one that works for this example - but I'm wondering how to do something a bit more...The solution provided used a case statement where the value in ColP was known. What happens if the value isn't known at design time?
The same thing that you would have to do with a Pivot... either change the code or write a dynamic solution... you'll find that writing a dynamic solution for a Crosstab is a lot easier than writing a dynamic solution for a Pivot.
--Jeff Moden
July 17, 2008 at 10:13 pm
Thank You very much Jeff!!!
It worked.
Also, would like to thanks to all who responded to this request.
You guys are great!!!
July 17, 2008 at 11:50 pm
My pleasure... thank you for the feedback. 🙂
--Jeff Moden
July 18, 2008 at 12:36 am
Hi black,
Try this. It should work as per your result set.
CREATE TABLE tblPivot (fldDate DateTime, fldValue int)
INSERT INTO tblPivot (fldDate,fldValue)
SELECT '4/1/2008',4
UNION ALL
SELECT '4/3/2008',6
UNION ALL
SELECT '4/7/2008',9
UNION ALL
SELECT '4/2/2008',1
UNION ALL
SELECT '5/8/2008',4
UNION ALL
SELECT '5/3/2008',2
UNION ALL
SELECT '5/9/2008',6
UNION ALL
SELECT '5/5/2008',2
SELECT DateName(mm,fldDate) AS Month,SUM(P.fldValue) AS Total FROM(
SELECT *,ROW_NUMBER() OVER (PARTITION BY DATEPART(mm,fldDate) ORDER BY fldDate) AS RowNumber
FROM tblPivot) AS P
GROUP BY DateName(mm,fldDate),DATEPART(mm,fldDate)
---
July 18, 2008 at 6:24 am
sqluser (7/18/2008)
Hi black,Try this. It should work as per your result set.
CREATE TABLE tblPivot (fldDate DateTime, fldValue int)
INSERT INTO tblPivot (fldDate,fldValue)
SELECT '4/1/2008',4
UNION ALL
SELECT '4/3/2008',6
UNION ALL
SELECT '4/7/2008',9
UNION ALL
SELECT '4/2/2008',1
UNION ALL
SELECT '5/8/2008',4
UNION ALL
SELECT '5/3/2008',2
UNION ALL
SELECT '5/9/2008',6
UNION ALL
SELECT '5/5/2008',2
SELECT DateName(mm,fldDate) AS Month,SUM(P.fldValue) AS Total FROM(
SELECT *,ROW_NUMBER() OVER (PARTITION BY DATEPART(mm,fldDate) ORDER BY fldDate) AS RowNumber
FROM tblPivot) AS P
GROUP BY DateName(mm,fldDate),DATEPART(mm,fldDate)
---
The problem with something like that is it can make a huge mistake and, unless you know the limits of the problem, no one will ever catch the problem. The error is demonstrated by the following code if you study it against the output of the code...
CREATE TABLE tblPivot (fldDate DateTime, fldValue int)
INSERT INTO tblPivot (fldDate,fldValue)
SELECT '4/1/2008',4 UNION ALL
SELECT '4/3/2008',6 UNION ALL
SELECT '4/7/2008',9 UNION ALL
SELECT '4/2/2008',1 UNION ALL
SELECT '5/8/2008',4 UNION ALL
SELECT '5/3/2008',2 UNION ALL
SELECT '5/9/2008',6 UNION ALL
SELECT '5/5/2008',2 UNION ALL
SELECT '4/1/2009',4 UNION ALL -------------------
SELECT '4/3/2009',6 UNION ALL
SELECT '4/7/2009',9 UNION ALL
SELECT '4/2/2009',1 UNION ALL
SELECT '5/8/2009',4 UNION ALL
SELECT '5/3/2009',2 UNION ALL
SELECT '5/9/2009',6 UNION ALL
SELECT '5/5/2009',2
SELECT DateName(mm,fldDate) AS Month,SUM(P.fldValue) AS Total FROM(
SELECT *,ROW_NUMBER() OVER (PARTITION BY DATEPART(mm,fldDate) ORDER BY fldDate) AS RowNumber
FROM tblPivot) AS P
GROUP BY DateName(mm,fldDate),DATEPART(mm,fldDate)
That's what's known as a "scalability" problem... it won't give the correct answer for more than 1 year at a time. We had a 3rd party vendor that wrote such code... they did some pretty bad damage with it... took us a year to find out, of course.
Always plan on scalability...
--Jeff Moden
Viewing 15 posts - 1 through 15 (of 15 total)
You must be logged in to reply to this topic. Login to reply