SQL 数据透视表产生重复项

Question

开发人员，

我是数据透视表的新手，并且在重复方面遇到了一些问题。我的表格在旋转之前看起来像这样：

位置 | 食物
田纳西州 |梨
田纳西州 |橙色
佛罗里达州 |橙色
佛罗里达州 |苹果
弗吉尼亚州 | pear

这是要旋转的代码，运行良好：

SELECT PivotTable.location, [apple], [orange], [pear]
FROM
(SELECT location, food FROM someTable) as inventory
PIVOT
(COUNT(inventory.food) FOR inventory.location IN ([apple],[orange],[pear])) AS PivotTable

这会产生如下输出：

Location | 苹果 | 橙色 | 梨
田纳西州 | 0 | 1 | 1
佛罗里达州 | 1 | 1 | 0
弗吉尼亚州 | 0 | 0 | 1

正如我所说，效果很好。但是，我在原始表格中添加了新的注释列，如下所示：

位置 | 食物 | apple_comments | orange_comments | pear_comments

田纳西州 |梨|空 |空 |空
田纳西州 |橙色|空|非常多汁|空
佛罗里达州 |橙色|空|空|空
佛罗里达州 |苹果|清脆|空 |空
弗吉尼亚州 |梨|空 |空|美味

这是我修改后的数据透视表来解释评论：

SELECT PivotTable.location, [apple], [apple_comments], [orange], [orange_comments], [pear], [pear_comments]
FROM
(SELECT location, food, apple_comments, orange_comments, pear_comments FROM someTable) as inventory
PIVOT
(COUNT(inventory.food) FOR inventory.location IN ([apple],[orange],[pear])) AS PivotTable

这会产生如下输出：

位置 | 苹果 | apple_comments | 橙色 | Orange_comments | 梨 | Pear_comments
田纳西州 | 0 |空| 0 |空| 1 |空
田纳西州 | 0 |空 | 1 |非常多汁| 0 |空
佛罗里达州 | 0 |空 | 1 |空| 0 |空
佛罗里达州 | 1 |清脆| 1 |空| 0 |空
弗吉尼亚州 | 0 |空 | 1 |空 | 1 |因此

，本质上，当为存在多个位置的每个条目添加注释时，它会创建一个重复行。对于弗吉尼亚州，只有一个条目，因此该行结果很好。

看来我需要再做一次调整或其他什么。谁能就我哪里出错提出建议？

对不起。所需的输出应如下所示：

位置 | 苹果 | apple_comments | 橙色 | Orange_comments | 梨 | Pear_comments
田纳西州 | 0 |空 | 1 |非常多汁| 1 |空
佛罗里达州 | 1 |清脆| 1 |空| 0 |空
弗吉尼亚州 | 0 |空 | 1 |空 | 1 |美味

本质上，将重复项合并到一行中。

谢谢。

Answer 1

根本问题是，您实际上已经告诉编译器除了 food 列之外，还按 comment 列进行分组。有一些解决方案，例如将评论汇总到分隔列表中，如下所示：

Select location
    , Sum( Case When S.food = 'Apple' Then 1 Else 0 End ) As Apple
    , Stuff(
        (
        Select ', ' + S1.Apple_Comments
        From SomeTable As S1
        Where S1.location = S.location
            And S1.Apple_Comments Is Not Null
        Group By S1.Apple_Comments
        For Xml Path(''), type
        ).value('.','nvarchar(max)')
        , 1, 2, '') As Apple_Comments
    , Sum( Case When S.food = 'Orange' Then 1 Else 0 End ) As Orange
    , Stuff(
        (
        Select ', ' + S1.Orange_Comments
        From SomeTable As S1
        Where S1.location = S.location
            And S1.Orange_Comments Is Not Null
        Group By S1.Orange_Comments
        For Xml Path(''), type
        ).value('.','nvarchar(max)')
        , 1, 2, '') As Orange_Comments
    , Sum( Case When S.food = 'Pear' Then 1 Else 0 End ) As Pear
    , Stuff(
        (
        Select ', ' + S1.Pear_Comments
        From SomeTable As S1
        Where S1.location = S.location
            And S1.Pear_Comments Is Not Null
        Group By S1.Pear_Comments
        For Xml Path(''), type
        ).value('.','nvarchar(max)')
        , 1, 2, '') As Pear_Comments
From SomeTable As S
Group By S.location

Answer 2

找到答案（利用“with CTE”和 MAX 函数）：

;With CTE as (
SELECT PivotTable.location, [apple], [apple_comments], [orange], [orange_comments], [pear], [pear_comments]
FROM
(SELECT location, food, apple_comments, orange_comments, pear_comments FROM someTable) as inventory
PIVOT
(COUNT(inventory.food) FOR inventory.location IN ([apple],[orange],[pear])) AS PivotTable)
select location, MAX([apple]) as [apple], MAX([apple_comments]) as [apple_comments],MAX([orange]) as [orange], 
MAX([orange_comments]) as [orange_comments], MAX([pear]) as [pear], MAX([pear_comments]) as [pear_comments]
from CTE group by location