Using Pivot

Question

Using Pivot

Jacob Pressures

SSCertifiable

Points: 5804
More actions
October 5, 2009 at 1:17 pm

#139231

I'm trying to pivot the GuardianName so that each Guardian appears on a single row with the StudentID.
Currently the resultset looks like this:
SID GuardianName RN
608 Brian Johnson 1
608 Kristi Johnson 2
609 Joan Baer 1
610 Elaine Stevens 1
610 Mike Stevens 2
611 Kathy Taylor 1
611 Patricia McIntyre 2
612 Robert Madison 1
612 Theresa Madison 2
I want it to look like this:
SID, Guardian1, Guardian2
608, Brian Johnson, Kristi Johnson
609, Joan Baer,
610, Elaine Stevens, Mike Stevens
611, Kathy Taylor, Patricia McIntyre
612, Robert Madison, Theresa Madison
(No concatenation above. Just commas to separate values.)
However, I get an error such as this:
Msg 156, Level 15, State 1, Line 20
Incorrect syntax near the keyword 'FOR'.
What am I doing wrong?
Also, is it against the rules to have a JOIN statement at all in the main query when using PIVOT?
;
WITH Guardian
AS
(
SELECT
sc.StudentID,
GuardianName=REPLACE(scp.FirstName + ' ' + scp.LastName,',',' '),
ROW_NUMBER() OVER(PARTITION BY sc.StudentID ORDER BY addr.State DESC, scp.LastName, scp.FirstName) AS rn
FROM StudentContact sc
INNER JOIN Person scp ON scp.PersonID = sc.ContactID
LEFT OUTER JOIN Address addr ON addr.AddressID = scp.MailingAddressID
LEFT OUTER JOIN PersonTelephone pt ON pt.PersonID = scp.PersonID AND pt.IsPrimary = 'True'
WHERE sc.IsCustodian = 1
)
select *
from guardian
PIVOT (
FOR RN
IN ([1], [2])
) AS pvt

Viewing 5 posts - 1 through 4 (of 4 total)

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

Garadin One Orange Chip Points: 29613 More actions · Answer 1

PIVOT needs to aggregate something. You're not aggregating anything, thus the error. You really don't need PIVOT at all, you can use something like this:

select g.studentid, g1.guardianname, g2.guardianname

from guardian g

LEFT JOIN guardian g1 ON g.studentid = g1.studentid AND g1.rn = 1

LEFT JOIN guardian g2 ON g.studentid = g2.studentid AND g2.rn = 2

Also, you're not using the join to PersonTelephone at all that I can see, you may want to remove that from your CTE.

Seth Phelabaum

Consistency is only a virtue if you're not a screwup. 😉

Links: How to Post Sample Data[/url] :: Running Totals[/url] :: Tally Table[/url] :: Cross Tabs/Pivots[/url] :: String Concatenation[/url]

Jacob Pressures SSCertifiable Points: 5804 More actions · Answer 2

So all i needed was a self join!!! I've done self joins but since all of this is still new to me, i get confused from time to time as to which to use when.

So I MUST have an aggregate in order to use PIVOT! Thanks!

ROW_NUMBER() OVER(PARTITION BY sc.StudentID ORDER BY scp.LastName, scp.FirstName) AS rn

Is the only purpose of PARTITION BY only to break the number sequence and start over? Does it have any other purpose? It just seems like a weird syntax to me.

Garadin One Orange Chip Points: 29613 More actions · Answer 3

Jacob Pressures (10/5/2009)
Is the only purpose of PARTITION BY only to break the number sequence and start over? Does it have any other purpose? It just seems like a weird syntax to me.

It does that for ROW_NUMBER. Here's some additional information:

http://msdn.microsoft.com/en-us/library/ms189461.aspx

Seth Phelabaum

Consistency is only a virtue if you're not a screwup. 😉

Links: How to Post Sample Data[/url] :: Running Totals[/url] :: Tally Table[/url] :: Cross Tabs/Pivots[/url] :: String Concatenation[/url]

Jacob Pressures SSCertifiable Points: 5804 More actions · Answer 4

Thanks guys! This was fantastic!!! I didn't understand everything at that link above, Seth, but all of this has helped me with a much more difficult query today. I couldn't respond yesterday because i lost my internet connection.

Guys thanks a million!:-)