How can i pivot this code?

Question

Post reply

How can i pivot this code?

simflex-897410

Hall of Fame

Points: 3881
More actions
March 12, 2014 at 7:24 pm

#289238

Hi,
When I run the following query:
SELECT ElectionName, Position, CandidateName, c.PositionId,CurrentOfficeHolder,AnswerType FROM Candidates c
INNER JOIN Positions p ON c.PositionId = p.PositionId
INNER JOIN Elections e on c.ElectionId = e.ElectionId
WHERE c.ElectionId IN (2,3)
GROUP BY Position, CandidateId, CandidateName,c.PositionId,CurrentOfficeHolder,AnswerType,ElectionName
ORDER BY c.PositionId
I get the following results:
GC BallMemberJiim Fyre7NULLM
GC BallMemberKatty Perry8NULLM
CF BallMemberJustine Util9NULLM
CF BallMemberBill Johnson10NULLM
CF BallMemberAnn Dawn11NULLM
How can I modify the code to show the results in the following format?
GC Ball
Jiim Fyre7NULLM
Katty Perry8NULLM
CF Ball
MemberJustine Util9NULLM
MemberBill Johnson10NULLM
MemberAnn Dawn11NULLM
Notice GC and CF are ElectionNames.
We would like to show each ElectionName just one, followed by associated results.
Your help is greatly appreciated.

Viewing 11 posts - 1 through 10 (of 10 total)

You must be logged in to reply to this topic. Login to reply

pietlinden SSC Guru Points: 63506 More actions · Answer 1

That's not a pivot.

Easiest way to do what you are describing is to use Reporting Services and add a Grouping level to your table.

simflex-897410 Hall of Fame Points: 3881 More actions · Answer 2

Thanks a lot for your response but this is not a reporting app.

Jeff Moden SSC Guru Points: 1004406 More actions · Answer 3

simflex-897410 (3/12/2014)
Hi,
When I run the following query:
SELECT ElectionName, Position, CandidateName, c.PositionId,CurrentOfficeHolder,AnswerType FROM Candidates c
INNER JOIN Positions p ON c.PositionId = p.PositionId
INNER JOIN Elections e on c.ElectionId = e.ElectionId
WHERE c.ElectionId IN (2,3)
GROUP BY Position, CandidateId, CandidateName,c.PositionId,CurrentOfficeHolder,AnswerType,ElectionName
ORDER BY c.PositionId
I get the following results:
GC BallMemberJiim Fyre7NULLM
GC BallMemberKatty Perry8NULLM
CF BallMemberJustine Util9NULLM
CF BallMemberBill Johnson10NULLM
CF BallMemberAnn Dawn11NULLM
How can I modify the code to show the results in the following format?
GC Ball
Jiim Fyre7NULLM
Katty Perry8NULLM
CF Ball
MemberJustine Util9NULLM
MemberBill Johnson10NULLM
MemberAnn Dawn11NULLM
Notice GC and CF are ElectionNames.
We would like to show each ElectionName just one, followed by associated results.
Your help is greatly appreciated.

You have nearly 700 visits. You've been around long enough to know that you should post data in a readily consumable format. Please see the first link in my signature line below under "Helpful Links" for how to do that properly in the future. You could have at least posted the result set as readily consumable data.

Guessing at everything because you didn't post the data in a readily consumable format, the following should be pretty close to what you need. I also recommend that anytime you have joins that you use the 2 part naming convention on all columns. I didn't even guess at that.

WITH

ctePreAgg AS

(

SELECT ElectionName, Position, CandidateName, c.PositionId,CurrentOfficeHolder,AnswerType

FROM Candidates c

JOIN Positions p ON c.PositionId = p.PositionId

JOIN Elections e on c.ElectionId = e.ElectionId

WHERE c.ElectionId IN (2,3)

GROUP BY Position, CandidateId, CandidateName,c.PositionId,CurrentOfficeHolder,AnswerType,ElectionName

)

SELECT [ElectionName/Position] = CASE WHEN GROUPING(mh.PositionId) = 0 THEN MAX(mh.Position) ELSE mh.ElectionName END

,CandidateName = CASE WHEN GROUPING(mh.PositionId) = 0 THEN MAX(mh.CandidateName) ELSE '' END

,PositionId = CASE WHEN GROUPING(mh.PositionId) = 0 THEN CAST(mh.PositionId AS VARCHAR(10)) ELSE '' END

,CurrentOfficeHolder = CASE WHEN GROUPING(mh.PositionId) = 0 THEN MAX(ISNULL(mh.CurrentOfficeHolder,'NULL')) ELSE '' END

,AnswerType = CASE WHEN GROUPING(mh.PositionId) = 0 THEN MAX(mh.AnswerType) ELSE '' END

FROM #MyHead mh

GROUP BY GROUPING SETS (ROLLUP(ElectionName,PositionId))

HAVING GROUPING(mh.ElectionName) = 0

ORDER BY mh.ElectionName, GROUPING(mh.PositionId) DESC, mh.PositionID

;

Again, guessing at what the real data looks like, you might be able to remove the GROUP BY from the cte.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

Jeff Moden SSC Guru Points: 1004406 More actions · Answer 4

pietlinden (3/12/2014)
That's not a pivot.
Easiest way to do what you are describing is to use Reporting Services and add a Grouping level to your table.

So let's see the easiest way. Post the solution that you would use for SSRS. 😉

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

simflex-897410 Hall of Fame Points: 3881 More actions · Answer 5

Hi Jeff,

Thanks so much for your help sir.

Yes, you are right; I should know better - sorry about that.

I am trying to test your code but keep getting errors.

First I ran it on 2005 SQL box and got "Incorrect syntax near SETS"

I suspected I was having that error because GROUPING SETS is not compatible with sql server 2005 or earlier.

Then I tried testing it with Sql server 2008 but now I get Invalid object name '#MyHead'.

I am more than happy to post some data if I am not getting the correct results but I appreciate your help resolving the errors.

Thanks a lot again, Jeff

UPDATE: I think I can replace #MyHead mh with ctePreAgg mh

Jeff Moden SSC Guru Points: 1004406 More actions · Answer 6

simflex-897410 (3/13/2014)
Hi Jeff,
Thanks so much for your help sir.
Yes, you are right; I should know better - sorry about that.
I am trying to test your code but keep getting errors.
First I ran it on 2005 SQL box and got "Incorrect syntax near SETS"
I suspected I was having that error because GROUPING SETS is not compatible with sql server 2005 or earlier.
Then I tried testing it with Sql server 2008 but now I get Invalid object name '#MyHead'.
I am more than happy to post some data if I am not getting the correct results but I appreciate your help resolving the errors.
Thanks a lot again, Jeff
UPDATE: I think I can replace #MyHead mh with ctePreAgg mh

You're correct. You posted in a 2008 forum and I assumed you had 2008. You should avoid doing such a thing in the future. A lot of us monitor all the forums so there's no need to try to post where you think the most activity is.

To fix the problems you're having, change the GROUP BY line in the outer query to...

GROUP BY ElectionName,PositionId WITH ROLLUP

... and, yeah, change #MyHead to the cte like you said.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

simflex-897410 Hall of Fame Points: 3881 More actions · Answer 7

Thanks Jeff,

My fault. My reasoning was that our production server hosts sql server 2008.

So, I figured it is better to have a sql server 2008 solution than 2005 because worst case scenario, I test my code in production server which was exactly what I did.

Today, our dba will be upgrading my test server to 2008 so I don't have to deal with this type of conflict.

You are great, sir.

Just one note sir. I think casting PositionId is creating a problem for me on the front end.

I am getting:

Exception Details: System.FormatException: Input string was not in a correct format.

Can this work without casting?

Please forgive me newbie questions, Jeff

Thanks for your help.

UPDATE: Jeff, I think this worked for me. It just needs your blessing.

,PositionId = CASE WHEN GROUPING(mh.PositionId) = 0 THEN mh.PositionId ELSE 0 END

Jeff Moden SSC Guru Points: 1004406 More actions · Answer 8

simflex-897410 (3/13/2014)
Thanks Jeff,
My fault. My reasoning was that our production server hosts sql server 2008.
So, I figured it is better to have a sql server 2008 solution than 2005 because worst case scenario, I test my code in production server which was exactly what I did.
Today, our dba will be upgrading my test server to 2008 so I don't have to deal with this type of conflict.
You are great, sir.
Just one note sir. I think casting PositionId is creating a problem for me on the front end.
I am getting:
Exception Details: System.FormatException: Input string was not in a correct format.
Can this work without casting?
Please forgive me newbie questions, Jeff
Thanks for your help.
UPDATE: Jeff, I think this worked for me. It just needs your blessing.
,PositionId = CASE WHEN GROUPING(mh.PositionId) = 0 THEN mh.PositionId ELSE 0 END

That will, of course, work but it will leave a 0 on the same row as the election name. That's why I specifically cast it to a varchar and substituted a blank. The front end code is probably looking for an INT instead of a VARCHAR. Personally, I'd make them change the front end. Better yet, I'd probably have them do this "titled subsection" type of thing in the front-end rather than doing it with T-SQL.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

simflex-897410 Hall of Fame Points: 3881 More actions · Answer 9

You are a life saver extraordinaire.

Thanks for your code, your explanations and patience.

It is working a like a dream!

There is 0 chance that position id is null or 0.

Thanks a lot Jeff.

Jeff Moden SSC Guru Points: 1004406 More actions · Answer 10

You bet. Thank you for the feedback.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)