我的PIVOT返回每行中具有数据透视列值的重复行

I am having an issue I have seen others with similar issues, but the answers for those don't seem to apply to my situation. This is my first Question, so forgive me in advance for any formatting issues and thanks for any insight you can provide.

我有一个问题,我见过其他有类似问题的人,但这些问题的答案似乎并不适用于我的情况。这是我的第一个问题,请原谅我提前解决任何格式问题,并感谢您提供的任何见解。

My #TempTBData looks like this:

我的#TempTBData看起来像这样:

InvoiceProductID ContactID ContactName ChargeDescription Amount
191 1832    Gloria Cheung   Cruise Fare 500.00
191 1886    John Novosad    Cruise Fare 500.00
191 2011    Christopher Yong    Cruise Fare 100.00

My pivot code looks like this:

我的透视代码如下所示:

SELECT DISTINCT<br>
      [InvoiceProductID]<br>
      ,[ChargeDescription]<br>
      ,[Christopher Yong],[Gloria Cheung],[John Novosad]<br>
      FROM #TempTBData<br>
          PIVOT(MAX([Amount])<br>
          FOR [ContactName] IN ([Christopher Yong],[Gloria Cheung],[John Novosad])) AS PVTTable

..And my PIVOT result looks like this:

..我的PIVOT结果如下:

 InvoiceProductID ChargeDescription Christopher Yong Gloria Cheung John Novosad

    191 Cruise Fare NULL    NULL    500.00
    191 Cruise Fare NULL    500.00  NULL
    191 Cruise Fare 100.00  NULL    NULL

..And I would like the result to be :

..我希望结果如下:

InvoiceProductID ChargeDescription Christopher Yong Gloria Cheung John Novosad
191 Cruise Fare 100.00  500.00  500.00

Please let me know what I am doing wrong.

请让我知道我做错了什么。

1 个解决方案

#1

The problem is caused by field ContactID of your table. Use a derived table instead that explicitly selects only fields necessary for pivot operation:

问题是由您的表的字段ContactID引起的。使用派生表而不是显式选择仅进行透视操作所需的字段:

SELECT [InvoiceProductID], [ChargeDescription],
       [Christopher Yong],[Gloria Cheung],[John Novosad]
FROM (
  SELECT [InvoiceProductID], [ContactName], [ChargeDescription], [Amount]
  FROM #TempTBData ) AS src
PIVOT(MAX([Amount])
FOR [ContactName] IN ([Christopher Yong],[Gloria Cheung],[John Novosad])) AS PVTTable

I've omitted DISTINCT since it seems to be redundant in this case.

我省略了DISTINCT,因为在这种情况下它似乎是多余的。

Demo here

#1