Crosstab function with monthly data

I have a table that has the following data:

PMG_BG       R402_Error_E      R402_Error_W      Month
-----------       ----------------       -----------------       --------
ABC              10                        120                        4
ABC              8                          65                          3
ABD              21                        22                          3
ABD              124                      8                            4
ABD              76                        121                        4

I would like to either alter my current select statment or a new statment that will give me the following result by summing the row entries and grouping them by months

PMG_BG       Month3      Month4
-----------       ---------     ---------
ABC              145           86
ABD              84             101

Note: (totals are not accurate)

SELECT     PMG_BG, R402_Error_E, R402_Error_W, Month
FROM         dbo.MONTHLY_TRENDS
WHERE     (Month >= MONTH(GETDATE()) - 2) AND (Month < MONTH(GETDATE()))

Open in new window

FairfieldAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Kevin CrossChief Technology OfficerCommented:

SELECT PMG_BG
, [3] AS Month3
, [4] AS Month4
FROM (
SELECT     PMG_BG, R402_Error_E + R402_Error_W AS R402_Error, Month
FROM         dbo.MONTHLY_TRENDS
WHERE     (Month >= MONTH(GETDATE()) - 2) AND (Month < MONTH(GETDATE()))
) t 
PIVOT (SUM(R402_Error) FOR Month IN ([4],[3])) pvt

Open in new window

0
Kevin CrossChief Technology OfficerCommented:
0
FairfieldAuthor Commented:
Is there a way to perform this by using a view?
0
Cloud Class® Course: C++ 11 Fundamentals

This course will introduce you to C++ 11 and teach you about syntax fundamentals.

Kevin CrossChief Technology OfficerCommented:
What do you mean?

The code above can be put into a view no problem.  Per my link on dynamically rolling the period, you can have the entire code in your view and it comes out pivoted already for you.  If that is not what you mean, please explain.
0
FairfieldAuthor Commented:
If I put this code directly into a view, this is the error I receive.

SQL text cannot be represented in the grid pane and diagram pane.
0
Kevin CrossChief Technology OfficerCommented:
If you mean you have a view already to get the data that you don't want to change SQL for, you can look at Mark's article that I referenced in mine above regarding creating a stored procedure which does the appropriate Dynamic SQL to pivot your view.

http:/articles/Microsoft/Development/MS-SQL-Server/SQL-Server-2005/Dynamic-Pivot-Procedure-for-SQL-Server.html
0
Kevin CrossChief Technology OfficerCommented:
But does it run correctly when you open the view for display?  Does it prevent you from saving.  As far as I know that message just says it won't represent the view design visually.

You can try it this way instead.  I normally do mine with a common table expression, but in the article and here I did with derived table to not have to get into CTE if you were not already use to using that.
WITH t AS (
SELECT     PMG_BG, R402_Error_E + R402_Error_W AS R402_Error, Month
FROM         dbo.MONTHLY_TRENDS
WHERE     (Month >= MONTH(GETDATE()) - 2) AND (Month < MONTH(GETDATE()))
)
SELECT PMG_BG
, [3] AS Month3
, [4] AS Month4
FROM t 
PIVOT (SUM(R402_Error) FOR Month IN ([4],[3])) pvt

Open in new window

0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server 2008

From novice to tech pro — start learning today.