SQL Pivot with String
pivot, sql
Solution
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.
Problem
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