SQL Pivot with String

Question

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 1

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.

Answer 2

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'