动态SQL透视表问题求助：YYYYMM列布局无法正常运行

Hey there! Static pivots work like a charm, but dynamic ones can trip you up over tiny details—let's figure out why yours isn't playing nice with the PERIOD table, and get it working smoothly.

Common Culprits for Dynamic Pivot Failure #

First, let's cover the most likely issues that break dynamic pivots tied to a period table:

• Missing column name formatting: YYYYMM values (like 201801) are numeric, so they need to be wrapped in brackets to be valid SQL column names.
• Duplicate or invalid period data: If your PERIOD table has duplicate months or malformed values, the column list gets messed up.
• Sloppy SQL string concatenation: Extra commas, missing spaces, or incorrect joins will break the final dynamic query.
• Incorrect pivot logic: Mismatched grouping fields or aggregation functions between static and dynamic versions.

Step-by-Step Fixed Example #

Here's a revised version of your code that addresses these issues, plus debugging steps to catch errors early:

-- Reset variables to avoid leftover values from previous runs
DECLARE @ColumnNames NVARCHAR(MAX) = '';
DECLARE @SQL NVARCHAR(MAX) = '';

-- 1. Build valid, unique column names from PERIOD table
-- Use QUOTENAME to wrap YYYYMM values (critical for numeric period IDs)
-- Add DISTINCT to avoid duplicate columns from repeated periods
SELECT @ColumnNames += QUOTENAME(CAST(p.period_column AS NVARCHAR(6))) + ','
FROM (
    SELECT DISTINCT period_column
    FROM PERIOD
    -- Filter to only the last 12 months (adjust logic to match your date range)
    WHERE period_column >= FORMAT(DATEADD(YEAR, -1, GETDATE()), 'yyyyMM')
      AND period_column <= FORMAT(GETDATE(), 'yyyyMM')
) AS p;

-- Remove the trailing comma from the column list
SET @ColumnNames = LEFT(@ColumnNames, LEN(@ColumnNames) - 1);

-- 2. Build the full dynamic pivot query
SET @SQL = N'
SELECT 
    [YourGroupingField],  -- Replace with your actual grouping column (e.g., CustomerID, Category)
    ' + @ColumnNames + '
FROM (
    -- Base dataset: Join your consumption table with PERIOD
    SELECT
        c.YourGroupingField,
        p.period_column,
        c.ConsumptionAmount  -- Replace with your actual amount column
    FROM ConsumptionTable c
    JOIN PERIOD p ON c.PeriodField = p.period_column  -- Ensure join condition is correct!
    -- Match the same date filter as the column list
    WHERE p.period_column >= FORMAT(DATEADD(YEAR, -1, GETDATE()), ''yyyyMM'')
      AND p.period_column <= FORMAT(GETDATE(), ''yyyyMM'')
) AS SourceData
PIVOT (
    SUM(ConsumptionAmount)  -- Aggregation: match your static pivot''s logic
    FOR period_column IN (' + @ColumnNames + ')
) AS PivotTable;';

-- 3. Debug first! Print the generated SQL to check for errors
PRINT @SQL;

-- 4. Execute the dynamic query once you've verified the printed SQL is correct
EXEC sp_executesql @SQL;

Key Checks to Ensure Success #

1. Verify the PERIOD table data: Run SELECT DISTINCT period_column FROM PERIOD to confirm you have exactly the 12 YYYYMM values you need, no duplicates or blanks.
2. Check the join condition: Make sure the field linking your consumption table to PERIOD is the same data type (e.g., both are NVARCHAR(6) or INT). Mismatched types cause silent failures.
3. Review the printed SQL: Copy the output of PRINT @SQL and run it directly in SSMS—this will show you any syntax errors (like missing brackets, extra commas) that break the dynamic query.
4. Match static pivot logic: Ensure the grouping fields, aggregation function (SUM), and filters are identical to your working static pivot. Even a small difference here will cause discrepancies.

If you still run into issues, share the printed SQL output and a sample of your PERIOD/consumption table data—we can narrow it down further!

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