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:http://stackoverflow.com/questions/6267660/sql-query-to-convert-rows-into-columns
Thanks
Answer
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.