SQL Pivot with string

Question

I have two tables in SQL Server: client 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 result I need:

 CustomerID Firstname HomeCity OfficeCity ----------- ---------- ---------- ---------- 1 Andrew Waxhaw Nashville 2 George Philadelphia Null 

This is my request, but the correct result is not obtained:

 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 I get:

 CustomerID Firstname HomeCity OfficeCity ----------- ---------- ---------- ---------- 1 Andrew Waxhaw NULL 1 Andrew NULL Nashville 2 George Philadelphia Null 

As you can see, client 1 is ultimately displayed twice. Is it possible to get only one row for each client?

I looked through this example but did not help: http://stackoverflow.com/questions/6267660/sql-query-to-convert-rows-into-columns

thanks

Answer 1

It gives this line because you have an AddressID in the selection list for your subquery "P". Therefore, even if you do not have an AddressID, you select this at the top level, the PIVOT function is still grouped. 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 connection between the client and the address.

Answer 2

I would write this as follows:

 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'