分组数据透视结果

经过长时间的google搜索,并弄清楚如何将数据放入一个具有动态行标题的体面数据透视表中,我终于做到了这一点。

我唯一搞不清楚的是如何按[位置]对结果进行分组,以及如何用“零”替换NULL/0?

为了用0替换NULL,我在这一行中尝试了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