我在sql Server中有两个表：Customer和Address
客户表：
CustomerID FirstName LastName ----------- ---------- ---------- 1 Andrew Jackson 2 George Washington
地址表：
AddressID CustomerID AddressType City ----------- ----------- ----------- ---------- 1 1 Home Waxhaw 2 1 Office Nashville 3 2 Home Philadelphia
这是我需要的输出：
CustomerID Firstname HomeCity OfficeCity ----------- ---------- ---------- ---------- 1 Andrew Waxhaw Nashville 2 George Philadelphia Null
这是我的查询,但没有得到正确的结果：
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
这是我得到的结果：
CustomerID Firstname HomeCity OfficeCity ----------- ---------- ---------- ---------- 1 Andrew Waxhaw NULL 1 Andrew NULL Nashville 2 George Philadelphia Null
如您所见,客户1在最终结果中出现两次.每个客户只能获得一行吗？
我查了一下这个例子,但没有帮助：http：//stackoverflow.com/questions/6267660/sql-query-to-convert-rows-into-columns
谢谢
解决方法
它给出了这一行,因为你在子查询“P”的选择列表中有AddressID.因此,即使您在顶层没有AddressID选择此项,PIVOT功能仍然按其分组.您需要将其更改为：
SELECT CustomerID,Home as HomeCity,Office as OfficeCity
FROM ( SELECT C.CustomerID,A.City
FROM #Customer C,#Address A
WHERE C.CustomerID = A.CustomerID
) AS P
PIVOT
( MAX(city)
FOR AddressType in ([Home],[Office])
) AS PVT
虽然我倾向于使用显式的INNER JOIN而不是客户和地址之间的隐式连接.
