October 21, 2011 at 3:08 pm
If I have a customer table with 2 columns: Cust_ID, CustomerName
And a PhoneContact table with the following columns: PhoneID,Cust_ID (FK), Phone_Number
how wold I pivot to recieve the following record layout?
CustomerName, Phone_Number1, Phone_Number2, Phone_Number3
October 22, 2011 at 9:15 am
dndaughtery (10/21/2011)
If I have a customer table with 2 columns: Cust_ID, CustomerNameAnd a PhoneContact table with the following columns: PhoneID,Cust_ID (FK), Phone_Number
how wold I pivot to recieve the following record layout?
CustomerName, Phone_Number1, Phone_Number2, Phone_Number3
If you want a tested answer, please see the following article... I recommend it to everyone to get better answers quicker.
http://qa.sqlservercentral.com/articles/Best+Practices/61537/
For information and examples as to how Cross Tabs and Pivots actually work, please see the following article...
http://qa.sqlservercentral.com/articles/T-SQL/63681/
For information and examples as to how to do it all with unknown numbers of return columns, please see the following article...
http://qa.sqlservercentral.com/articles/Crosstab/65048/
If you absolutely need to solve your immediate problem with a single query, please see the following code. Please understand that I've not tested it because you didn't provide any readily consumable test data. Like the comments in the code say, I don't actually recommend that people do this as a single query...
--===== If you've really got to have it as an "all in one query" (and people rarely do),
-- then this will work but it'll be a fair bit slower, comparatively speaking.
WITH
cteEnumerate AS
(
SELECT RowNum = ROW_NUMBER() OVER (PARTITION BY c.Cust_ID ORDER BY (SELECT NULL)),
c.CustomerID,
c.CustomerName,
p.Phone_Number
FROM dbo.Customer c
LEFT JOIN dbo.PhoneContact
ON c.Cust_ID = p.Cust_ID
)
SELECT CustomerName,
Phone_Number1 = MAX(CASE WHEN RowNum = 1 THEN Phone_Number ELSE '' END),
Phone_Number2 = MAX(CASE WHEN RowNum = 2 THEN Phone_Number ELSE '' END),
Phone_Number3 = MAX(CASE WHEN RowNum = 3 THEN Phone_Number ELSE '' END)
FROM cteEnumerate
GROUP BY CustomerName
ORDER BY CustomerName
;
Here's a "Divide'n'Conquer" method that's comparatively faster than the code above. There's one faster method than even what follows but most folks already think I'm nuts for splitting code up to get performance out of the code to begin with :-P. Again, the code is untested for the same reason as before...
--===== Combine the customer and phone information and number the entries by customer.
-- Yes, this could be done as a CTE but it would be comparatively much slower.
-- It also allows for the bit of error checking that follows.
-- The reason for the LEFT JOIN is to show customers with no phone numbers
SELECT RowNum = ROW_NUMBER() OVER (PARTITION BY c.Cust_ID ORDER BY (SELECT NULL)),
c.CustomerID,
c.CustomerName,
p.Phone_Number
INTO #Enumerate
FROM dbo.Customer c
LEFT JOIN dbo.PhoneContact
ON c.Cust_ID = p.Cust_ID
;
--===== If we have customers with more than 3 phone numbers, error out.
IF EXISTS (SELECT 1 FROM #Enumerate WHERE RowNum > 3)
BEGIN
SELECT CustomerID, CustomerName FROM #Enumerate WHERE RowNum = 4;
RAISERROR ('The customers above have more than 3 telephone numbers.',16,1);
END
;
--===== If we made it to here, we're good to go... pivot the data as requested
-- Even this could be sped up more but you probably think I'm nuts already
-- because I split this problem up instead of doing it all in one query. ;-)
SELECT CustomerName,
Phone_Number1 = MAX(CASE WHEN RowNum = 1 THEN Phone_Number ELSE '' END),
Phone_Number2 = MAX(CASE WHEN RowNum = 2 THEN Phone_Number ELSE '' END),
Phone_Number3 = MAX(CASE WHEN RowNum = 3 THEN Phone_Number ELSE '' END)
FROM #Enumerate
GROUP BY CustomerName
ORDER BY CustomerName
;
--Jeff Moden
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply