带有字符串的SQL Pivot

客户表：

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

谢谢

解决方法

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而不是客户和地址之间的隐式连接.

（编辑：李大同）

【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!