I have two tables in SQL Server: Customer and Address
Customer Table:
CustomerID FirstName LastName ----------- ---------- ---------- 1 Andrew Jackson 2 George Washington Address Table:
AddressID CustomerID AddressType City ----------- ----------- ----------- ---------- 1 1 Home Waxhaw 2 1 Office Nashville 3 2 Home Philadelphia This is the output that I need:
CustomerID Firstname HomeCity OfficeCity ----------- ---------- ---------- ---------- 1 Andrew Waxhaw Nashville 2 George Philadelphia Null This is my query, but not getting the right result:
SELECT CustomerID, Firstname, HOme as HomeCity, Office as OfficeCity FROM (SELECT C.CustomerID, C.FirstName, A.AddressID, A.AddressType, A.City FROM Customer C, Address A WHERE C.CustomerID = A.CustomerID)as P PIVOT (MAX(city) FOR AddressType in ([Home],[Office])) as PVT This is the result that I am getting:
CustomerID Firstname HomeCity OfficeCity ----------- ---------- ---------- ---------- 1 Andrew Waxhaw NULL 1 Andrew NULL Nashville 2 George Philadelphia Null As you can see Customer 1 is showing up twice in the final result. Is it possible to get only one row per customer?
I looked up this example, but didn't help:
Thanks
2 Answers
It is giving this row because you have AddressID in the select list for you subquery "P". So even though you don't have AddressID in you top level select this, the PIVOT function is still grouping by it. You need to change this to:
SELECT CustomerID, Firstname, Home as HomeCity, Office as OfficeCity FROM ( SELECT C.CustomerID, C.FirstName, A.AddressType, A.City FROM #Customer C, #Address A WHERE C.CustomerID = A.CustomerID ) AS P PIVOT ( MAX(city) FOR AddressType in ([Home],[Office]) ) AS PVT Although I would be inclined to use an explicit INNER JOIN rather than an implicit join between customer and Address.
I would write it like this instead:
SELECT C.CustomerID, C.Firstname, Home.City as HomeCity, Office.City as OfficeCity FROM Customer C LEFT JOIN Address Home on Home.CustomerID = C.CustomerID and Home.AddressType = 'Home' LEFT JOIN Address Office on Office.CustomerID = C.CustomerID and Office.AddressType = 'Office' 
