My PIVOT is returning duplicate rows with pivot column values in each row

Question

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:

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:

 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.

Answer 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:

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.

Demo here