带有字符串的SQL Pivot [英] SQL Pivot with String

问题描述

我在SQL Server中有两个表:Customer和Address

I have two tables in SQL Server: Customer and 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

这是我的查询，但没有得到正确的结果:

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

这是我得到的结果:

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

您可以看到客户1在最终结果中出现了两次.每个客户只能获得一行吗?

As you can see Customer 1 is showing up twice in the final result. Is it possible to get only one row per customer?

我查看了此示例，但没有帮助:http://stackoverflow.com/questions/6267660/sql-query-to-convert-rows-into-columns

I looked up this example, but didn't help:http://stackoverflow.com/questions/6267660/sql-query-to-convert-rows-into-columns

谢谢

推荐答案

给出该行是因为在子查询"P"的选择列表中有AddressID.因此，即使您没有在顶层中选择的AddressID，PIVOT函数仍按它分组.您需要将其更改为:

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

尽管我倾向于在客户和地址之间使用显式的INNER JOIN而不是隐式的联接.

Although I would be inclined to use an explicit INNER JOIN rather than an implicit join between customer and Address.

这篇关于带有字符串的SQL Pivot的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！