Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Pivot results not as expected Expand / Collapse
Author
Message
Posted Monday, January 28, 2013 9:09 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Thursday, September 19, 2013 1:22 PM
Points: 16, Visits: 138
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.
Post #1412492
Posted Tuesday, January 29, 2013 2:21 AM


Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Yesterday @ 6:01 PM
Points: 3,609, Visits: 5,222
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!
Post #1412828
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse