动态行标题数据透视表问题：按Location分组及NULL替换为0失败

Hey there! Let's work through your two pivot table challenges step by step:

It sounds like your ISNULL() and COALESCE() attempts aren't landing—let's break down why and fix it.

First, the order of your functions might be causing issues. Your current code tries to CAST and ROUND before handling NULLs, which can lead to unexpected NULLs if the original value is NULL (or if the cast fails). Try reversing the flow: handle the NULL first, then do your conversion and rounding.

Here are tailored solutions for common scenarios:

• If [Remaining Quantity] is a numeric type (int, float, etc.):
  Skip unnecessary casting and directly replace NULLs before rounding:

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

• If [Remaining Quantity] is a string type (with numeric values or 'NULL' text):
  Use TRY_CAST to safely convert strings to numbers (it returns NULL if conversion fails), then catch NULLs:

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

  If your "NULLs" are actually the string 'NULL' (not true SQL NULL values), add NULLIF to catch those text entries:

  SELECT COALESCE(ROUND(TRY_CAST(NULLIF([Remaining Quantity], 'NULL') AS decimal(10, 1)), 1), 0) AS [Remaining QuantityRound], *
  

To group your dynamic pivot table by [Location], you need to include it in both your source data and grouping logic. Here's how to adjust a dynamic pivot query to make this work:

DECLARE @DynamicColumns NVARCHAR(MAX), @FullSQL NVARCHAR(MAX)

-- Fetch your dynamic row headers
SELECT @DynamicColumns = STRING_AGG(QUOTENAME(RowHeader), ', ')
FROM (SELECT DISTINCT RowHeader FROM YourSourceTable) AS HeaderList

-- Build the pivot query with Location grouping
SET @FullSQL = N'
SELECT [Location], ' + @DynamicColumns + '
FROM (
    -- Include Location in your source dataset
    SELECT [Location], RowHeader, [Remaining Quantity]
    FROM YourSourceTable
) AS PivotSource
PIVOT (
    -- Use your preferred aggregation function (SUM, AVG, etc.)
    SUM([Remaining Quantity])
    FOR RowHeader IN (' + @DynamicColumns + ')
) AS PivotedResults
-- Group by Location and all dynamic pivot columns
GROUP BY [Location], ' + @DynamicColumns + '
'

-- Run the dynamic query
EXEC sp_executesql @FullSQL

This ensures your pivot results are organized by each unique Location value, with your dynamic row headers nested under each group. Just double-check that [Location] doesn't have unintended whitespace or typos in your source data—small inconsistencies can break grouping without obvious errors!

内容的提问来源于stack exchange，提问作者Bldjef