January 28, 2013 at 9:09 am
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
02123456 111239871
02456789111236542
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:
January 29, 2013 at 2:21 am
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 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?[/url]
Since random numbers are too important to be left to chance, let's generate some![/url]
Learn to understand recursive CTEs by example.[/url]
[url url=http://www.sqlservercentral.com/articles/St
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply