Pivot results not as expected

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » SQL Server 2008 » SQL Server Newbies » Pivot results not as expected

Pivot results not as expected

Rate Topic

Display Mode

Topic Options

Author

Message

jillk

Posted Monday, January 28, 2013 9:09 AM

Grasshopper

Group: General Forum Members
Last Login: Thursday, April 18, 2013 8:55 AM
Points: 16, Visits: 133

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

Post #1412492

dwain.c

Posted Tuesday, January 29, 2013 2:21 AM

SSCrazy

Group: General Forum Members
Last Login: Yesterday @ 7:24 PM
Points: 2,346, Visits: 3,192

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

No loops! No CURSORs! No RBAR! Hoo-uh!

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?

Need to UNPIVOT? Why not CROSS APPLY VALUES instead?
Since random numbers are too important to be left to chance, let's generate some!
Are you too recursively challenged?
Splitting strings based on patterns can be fast!

Post #1412828

« Prev Topic | Next Topic »

Permissions