组透视结果

Question

经过长时间的谷歌搜索并弄清楚如何将我的数据放入带有动态行标题的体面数据透视表中，我走到了这一步。

我唯一无法弄清楚的是如何按 [位置] 对结果进行分组以及如何将 NULL 替换为"零"/0？

为了将 NULL 替换为 0，我在此行中尝试了 ISNULL(( 和 COALESCE((，但它不会更改 NULL：

    SELECT COALESCE(ROUND(CAST([Remaining Quantity] AS decimal (2,0)), 1),0) AS [Remaining QuantityRound], * 

或

    SELECT ISNULL(ROUND(CAST([Remaining Quantity] AS decimal (2,0)), 1),0) AS [Remaining QuantityRound], * 

我现在有的 SQL 查询：

    DECLARE @DynamicPivotQuery AS NVARCHAR(MAX)
    DECLARE @ColumnName AS NVARCHAR(MAX)
    declare @item varchar(max);
    declare @open varchar(max);
    set @item = 291557
    set @open = 1
    --Get distinct values of the PIVOT Column 
    SELECT @ColumnName= ISNULL(@ColumnName + ',','') 
           + QUOTENAME([Size])
    FROM (SELECT DISTINCT [Size] FROM [Table] WHERE [Item] = @item AND [Open] = @open) AS Items
    --Prepare the PIVOT query using the dynamic 
    SET @DynamicPivotQuery = 
      'SELECT [Location], ' + @ColumnName + '
        FROM 
        (SELECT ROUND(CAST([Quantity] AS decimal (2,0)), 1) AS [QuantityRound], * FROM [Table]  
        WHERE [Item] = ''' + @item + ''' AND [Open] = ''' + @open + ''') x
        PIVOT(SUM([QuantityRound])
              FOR [Size] IN (' + @ColumnName + ')) AS PVTTable'
    --Execute the Dynamic Pivot Query
    EXEC sp_executesql @DynamicPivotQuery

结果：

    Location   S      M        L
    001        1      NULL     NULL
    001        NULL   1        NULL
    002        NULL   NULL     2
    002        NULL   1        NULL

我想要实现的目标：

    Location   S      M        L
    001        1      1        0
    002        0      1        2

Answer 1

删除子查询中的*：

(SELECT ROUND(CAST([Quantity] AS decimal (2,0)), 1) AS [QuantityRound], SIZE, LOCATION FROM [Table]  

额外的

列会导致额外的行。