Pivoting in SQL Server 2008

Question

This is my SQL script for pivoting

DECLARE @CanvassQuery AS NVARCHAR(MAX)
DECLARE @SupplierName AS NVARCHAR(MAX)

SELECT 
    @SupplierName= ISNULL(@SupplierName + ',','') + QUOTENAME(SupplierName)
FROM 
    (SELECT DISTINCT SupplierName 
     FROM dpiItemCS_Detailed) AS Supplier

SET @CanvassQuery =  N'SELECT ItemDescription, ' + @SupplierName + '
                       FROM dpiItemCS_Detailed
                       PIVOT(SUM(UnitCost) FOR SupplierName IN (' + @SupplierName + '))  AS CanvassTable'

EXEC sp_executesql @CanvassQuery

The output is

+-------------+-----------+------------+-----------+
| Description | Supplier1 | Supplier2  | Supplier3 |
+-------------+-----------+------------+-----------+
| Item 1      | 28.25     | null       | null      |
| Item 1      | null      | 28.50      | null      |
| Item 1      | null      | null       | 28.75     |
+-------------+-----------+------------+-----------+

I want my output to be

+-------------+-----------+-----------+-----------+
| Description | Supplier1 | Supplier2 | Supplier3 |
+-------------+-----------+-----------+-----------+
| Item 1      | 28.25     | 28.50     | 28.75     |
+-------------+-----------+-----------+-----------+

What is the correct SQL statement for pivoting to get this output?

Answer 1

Without seeing your table structure, I'm guessing that you have some column in dpiItemCS_Detailed that is unique (maybe an ID column) which is being used when grouping the PIVOT. In order to fix this, you will want to use a subquery in your PIVOT code. The code should be:

DECLARE @CanvassQuery AS NVARCHAR(MAX)
DECLARE @SupplierName AS NVARCHAR(MAX)

SELECT 
    @SupplierName= ISNULL(@SupplierName + ',','') + QUOTENAME(SupplierName)
FROM 
    (SELECT DISTINCT SupplierName 
     FROM dpiItemCS_Detailed) AS Supplier

SET @CanvassQuery =  N'SELECT ItemDescription, ' + @SupplierName + '
                       FROM
                       (
                         SELECT ItemDescription, UnitCost, SupplierName
                         FROM dpiItemCS_Detailed
                       ) d
                       PIVOT(SUM(UnitCost) 
                       FOR SupplierName IN (' + @SupplierName + '))  AS CanvassTable'

EXEC sp_executesql @CanvassQuery

When you are using PIVOT, you only want to include the columns needed for the final select list and pivot aggregation in your dataset - including other columns could potentially skew your result.