You are here:

MS SQL Server/SQL Server 2005 Pivot table on multiple aggregate columns

Advertisement


Question
QUESTION: Is it possible to pivot a sql server query based on multiple aggregate columns. Below is the sql that I am using. When pivoting on one aggregate column it works, but when I try more then 1 it does not work. When I tried to use the field name and not the aggregate I get the following "is not a recognized aggregate function."

Select *
From (
Select
CsuPSID As PSID,
--Year(CsuDateBeg) As Ck_Year,
Month(CsuDateBeg) As Ck_Month,
--CsuFlxID As Ck_Count,
IsNull(CsuCurrGrossPay, 0) As Gross--,
--IsNull(CsuCurrNetPay, 0) As Net
From
CheckSumm
Where
ISNUMERIC(CsuPSID) = 0
And Year(CsuDateBeg) = '2014'
And CsuDateBeg <= GetDate()
--Group by
--CsuPSID,
--Year(CsuDateBeg),
--Month(CsuDateBeg)
--Order By
--Month(CsuDateBeg),
--CsuPSID
) AS s
PIVOT
(
  --COUNT(Ck_Count),
  SUM(Gross)--,
  --SUM(Net)
  For [Ck_Month]
     IN ([1], [2], [3], [4], [5], [6], [7], [8], [9], [10], [11], [12])
)AS spivot

Order By PSID


Any assistance you can provide would be greatly appreciated.

ANSWER: Well, this is embarrassing. Have you grown tired of waiting (and possibly gone on to have kids, a full career, and written a novel)? I apologize. I don't know how I let your question sit out there for so long.

If you still would like to explore this question, I'm game (but if you got an answer elsewhere, I'd fully understand).

I believe multiple UNIONed PIVOTs might be the best way to tackle what you're trying to do. If you want to keep going, send me some code to create sample data and we'll see what we can do.

Thanks and sorry again for the delay.

---------- FOLLOW-UP ----------

QUESTION: LOL....Loved your response...I am not familiar with union pivots Here is what I came up with. Basically I am trying to get the total check count, Gross, Net for anyone that got paid in 2004 by month.

Select *,
From (
Select
CsuPSID As PSID,
Year(CsuDateBeg) As Ck_Year,
Month(CsuDateBeg) As Ck_Month,
CsuFlxID As Ck_Count,
IsNull(CsuCurrGrossPay, 0) As Gross,
IsNull(CsuCurrNetPay, 0) As Net
From
CheckSumm
Where
ISNUMERIC(CsuPSID) = 0
And Year(CsuDateBeg) = '2014'
And CsuDateBeg <= GetDate()
) AS s (Ck_Count, Gross, Net)
PIVOT
(SUM(Gross, Net) For [Ck_Month] IN ([1], [2], [3])
))AS spivot

Order By PSID

Answer
Holy rolling raccoon poop... I just now noticed this unanswered followup from several years ago (OK, it was "only" several months, but still...).  I only noticed because I was notified of a new question and saw this in my "inbox". I'm not sure what happened, but the most likely answer is I was notified by AllExperts.com and made a mental note to check and then... just up and forgot.

I hope by now you've resolved your issue (but if not and you still want my input, by all means let me know).

Beyond that, all I can do is apologize profusely for dropping the ball. So... sorry about that!

-D

MS SQL Server

All Answers


Answers by Expert:


Ask Experts

Volunteer


David Vaughn

Expertise

Performance tuning; T-SQL syntax;

Experience

Over 25 years of IT experience, the last 15 as a SQL Server developer/DBA.

Education/Credentials
Truman State University

©2016 About.com. All rights reserved.