SQL Azure中如何移除全NULL值的月份列？技术求助

我在Microsoft SQL Server Management Studio里尝试移除所有值全为NULL的月份列，查了很多帖子要么找不到合适方案，要么看不懂。当前用的是Microsoft SQL Azure (RTM) - 12.0.2000.8（2024年5月11日发布）。附上查询结果截图，求帮忙解决，我的SQL代码如下：

-- Step 1: Identify months with holiday sales
WITH SalesWithStoreType AS 
(
    SELECT 
        S.Store,
        ST.Type,
        S.Date,
        S.Weekly_Sales
    FROM
        [dbo].[store_dept_sales] AS S
    JOIN
        [dbo].[stores] AS ST ON S.Store = ST.Store
    WHERE
        S.IsHoliday = 1
),
MonthlySales AS 
(
    SELECT
        Type,
        MONTH(S.Date) AS month,
        SUM(S.Weekly_Sales) AS total_sales
    FROM
        SalesWithStoreType S
    GROUP BY
        Type, MONTH(S.Date)
),
-- Step 2: Generate pivoted results for only the months with holiday sales
PivotedSales AS 
(
    SELECT
        Type,
        January = FORMAT(MAX(CASE WHEN month = 1 THEN total_sales ELSE NULL END), 'C'),
        February = FORMAT(MAX(CASE WHEN month = 2 THEN total_sales ELSE NULL END), 'C'),
        March = FORMAT(MAX(CASE WHEN month = 3 THEN total_sales ELSE NULL END), 'C'),
        April = FORMAT(MAX(CASE WHEN month = 4 THEN total_sales ELSE NULL END), 'C'),
        May = FORMAT(MAX(CASE WHEN month = 5 THEN total_sales ELSE NULL END), 'C'),
        June = FORMAT(MAX(CASE WHEN month = 6 THEN total_sales ELSE NULL END), 'C'),
        July = FORMAT(MAX(CASE WHEN month = 7 THEN total_sales ELSE NULL END), 'C'),
        August = FORMAT(MAX(CASE WHEN month = 8 THEN total_sales ELSE NULL END), 'C'),
        September = FORMAT(MAX(CASE WHEN month = 9 THEN total_sales ELSE NULL END), 'C'),
        October = FORMAT(MAX(CASE WHEN month = 10 THEN total_sales ELSE NULL END), 'C'),
        November = FORMAT(MAX(CASE WHEN month = 11 THEN total_sales ELSE NULL END), 'C'),
        December = FORMAT(MAX(CASE WHEN month = 12 THEN total_sales ELSE NULL END), 'C')
    FROM 
        MonthlySales
    GROUP BY 
        Type
)
SELECT
    Type,
    January,
    February,
    March,
    April,
    May,
    June,
    July,
    August,
    September,
    October,
    November,
    December
FROM 
    PivotedSales
WHERE
    January IS NOT NULL
    OR February IS NOT NULL
    OR March IS NOT NULL
    OR April IS NOT NULL
    OR May IS NOT NULL
    OR June IS NOT NULL
    OR July IS NOT NULL
    OR August IS NOT NULL
    OR September IS NOT NULL
    OR October IS NOT NULL
    OR November IS NOT NULL
    OR December IS NOT NULL
ORDER BY 
    type;

解决方案：使用动态SQL自动过滤空列 #

因为SQL静态查询没法自动移除全空列，我们可以用动态SQL实现——先找出所有有数据的月份，再自动生成只包含这些月份的查询语句。

以下是完整可运行代码，每一步都加了注释：

DECLARE @MonthColumns NVARCHAR(MAX)
DECLARE @SelectColumns NVARCHAR(MAX)
DECLARE @DynamicSQL NVARCHAR(MAX)

-- 第一步：获取所有有数据的月份（即至少有一个Type对应有销售额的月份）
;WITH SalesWithStoreType AS 
(
    SELECT 
        MONTH(S.Date) AS month
    FROM
        [dbo].[store_dept_sales] AS S
    JOIN
        [dbo].[stores] AS ST ON S.Store = ST.Store
    WHERE
        S.IsHoliday = 1
    GROUP BY
        MONTH(S.Date)
)
-- 生成PIVOT需要的月份列定义，以及SELECT时要输出的列名
SELECT 
    -- 生成CASE WHEN部分，用于PIVOT计算销售额并格式化
    @MonthColumns = STRING_AGG(
        CONCAT(
            '[', DATENAME(MONTH, DATEFROMPARTS(2000, month, 1)), '] = FORMAT(MAX(CASE WHEN month = ', month, ' THEN total_sales ELSE NULL END), ''C'')'
        ),
        ', '
    ),
    -- 生成最终SELECT的列名列表
    @SelectColumns = STRING_AGG(
        CONCAT('[', DATENAME(MONTH, DATEFROMPARTS(2000, month, 1)), ']'),
        ', '
    )
FROM SalesWithStoreType
ORDER BY month

-- 第二步：拼接完整的动态SQL语句
SET @DynamicSQL = CONCAT('
WITH SalesWithStoreType AS 
(
    SELECT 
        S.Store,
        ST.Type,
        S.Date,
        S.Weekly_Sales
    FROM
        [dbo].[store_dept_sales] AS S
    JOIN
        [dbo].[stores] AS ST ON S.Store = ST.Store
    WHERE
        S.IsHoliday = 1
),
MonthlySales AS 
(
    SELECT
        Type,
        MONTH(S.Date) AS month,
        SUM(S.Weekly_Sales) AS total_sales
    FROM
        SalesWithStoreType S
    GROUP BY
        Type, MONTH(S.Date)
),
PivotedSales AS 
(
    SELECT
        Type,
        ', @MonthColumns, '
    FROM 
        MonthlySales
    GROUP BY 
        Type
)
SELECT
    Type,
    ', @SelectColumns, '
FROM 
    PivotedSales
WHERE
    ', REPLACE(@SelectColumns, ', ', ' IS NOT NULL OR '), ' IS NOT NULL
ORDER BY 
    type;
')

-- 第三步：执行动态SQL
EXEC sp_executesql @DynamicSQL

关键说明： #

1. STRING_AGG函数：SQL Azure支持该函数，用来把多行内容拼接成字符串，自动生成列列表，不用手动写12个月份。
2. DATENAME函数：把月份数字转换成对应的英文月份名（比如1转成January），和你原来的列名保持一致。
3. 动态SQL执行：用sp_executesql执行生成的SQL语句，这是SQL里安全执行动态代码的标准方式。
4. 空行过滤：WHERE条件自动生成，确保只保留至少有一个月份有数据的Type行。

注意事项： #

• 如果你没有执行动态SQL的权限，需要联系管理员开通。
• 代码里的2000只是用来生成月份名，随便选一个有12个月的年份就行，不影响结果。

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