SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Pivot results not as expected


Pivot results not as expected

Author
Message
jillk
jillk
Valued Member
Valued Member (71 reputation)Valued Member (71 reputation)Valued Member (71 reputation)Valued Member (71 reputation)Valued Member (71 reputation)Valued Member (71 reputation)Valued Member (71 reputation)Valued Member (71 reputation)

Group: General Forum Members
Points: 71 Visits: 141
I have a select statement as below:

SELECT
Institution_Number,
Attached_Account_Number,
[1],
[2],
[3]
FROM
(
SELECT
CARD_Attached_Acct.Institution_Number,
CARD_Attached_Acct.Card_Number,
CARD_Attached_Acct.Portfolio,
CARD_Attached_Acct.Attached_Account_Type,
CARD_Attached_Acct.Attached_Account_Number,
Inquiry_Name_To_DDA.Name_ID,
Inquiry_Name_To_DDA.Name_Line
FROM CARD_Attached_Acct
JOIN CARD_Acct
ON (CARD_Attached_Acct.Institution_Number = CARD_Acct.Institution_Number)
AND (CARD_Attached_Acct.Card_Number = CARD_Acct.Card_Number)
JOIN Inquiry_Name_To_Card
ON (CARD_Attached_Acct.Institution_Number = Inquiry_Name_To_Card.Institution_Number)
AND (CARD_Attached_Acct.Card_Number = Inquiry_Name_To_Card.Account_Number)
JOIN Inquiry_Name_To_DDA
ON (CARD_Attached_Acct.Institution_Number = Inquiry_Name_To_DDA.Institution_Number)
AND (CARD_Attached_Acct.Attached_Account_Number = Inquiry_Name_To_DDA.Account_Number)
AND (Inquiry_Name_To_Card.Name_ID = Inquiry_Name_To_DDA.Name_ID)
WHERE CARD_Acct.Status_Code = 'A' AND CARD_Attached_Acct.Attached_Account_Type = 1
AND CARD_Attached_Acct.Attached_Account_Number = 123

) AS SourceTable
PIVOT
(MAX(Card_Number) FOR Name_Line IN ([1], [2], [3])) AS PivotTable



When I run the inner Select statement I get results as below:
Institution_Number Card_Number Portfolio Attached_Account_Type Attached_Account_Number Name_ID Name_Line
02 123456 1 1 123 987 1
02 456789 1 1 123 654 2



My desired result when I run the entire script with the PIVOT is 1 line:
Institution_Number Attached_Account_Number 1 2 3
02 123 123456 456789



But what I get is this:
Institution_Number Attached_Account_Number 1 2 3
02 123 123456 NULL NULL
02 123 NULL 456789 NULL

I'd appreciate any help to get my desired result set! This is frustrating me.Crazy
dwain.c
dwain.c
SSCoach
SSCoach (17K reputation)SSCoach (17K reputation)SSCoach (17K reputation)SSCoach (17K reputation)SSCoach (17K reputation)SSCoach (17K reputation)SSCoach (17K reputation)SSCoach (17K reputation)

Group: General Forum Members
Points: 17413 Visits: 6431
I suggestion you make these changes:


SELECT
Institution_Number,
Attached_Account_Number,
[1]=MAX([1]),
[2]=MAX([2]),
[3]=MAX([3])

... (at the end add the following):

GROUP BY Institution_Number, Attached_Account_Number





My mantra: No loops! No CURSORs! No RBAR! Hoo-uh!

My thought question: Have you ever been told that your query runs too fast?

My advice:
INDEXing a poor-performing query is like putting sugar on cat food. Yeah, it probably tastes better but are you sure you want to eat it?
The path of least resistance can be a slippery slope. Take care that fixing your fixes of fixes doesn't snowball and end up costing you more than fixing the root cause would have in the first place.


Need to UNPIVOT? Why not CROSS APPLY VALUES instead?
Since random numbers are too important to be left to chance, let's generate some!
Learn to understand recursive CTEs by example.
Splitting strings based on patterns can be fast!
My temporal SQL musings: Calendar Tables, an Easter SQL, Time Slots and Self-maintaining, Contiguous Effective Dates in Temporal Tables
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search