在SQL中使用UNPIVOT时如何添加动态日期列别名？

方案1：行式输出（推荐，高效简洁） #

如果不需要把日期作为列名，而是每行展示日期和对应销售总数，这个写法只扫描一次sales表，比原查询效率高得多：

DECLARE @Today DATE = CAST(GETDATE() AS DATE)

SELECT
    CAST(createdTimestamp AS DATE) AS [销售日期],
    COUNT(id) AS [销售总数]
FROM sales
WHERE CAST(createdTimestamp AS DATE) BETWEEN DATEADD(DAY, -6, @Today) AND @Today
GROUP BY CAST(createdTimestamp AS DATE)
ORDER BY [销售日期] DESC;

方案2：列式输出（动态日期列名） #

如果必须保持原查询的列展示结构，用动态SQL实现动态日期列名，自动生成最近7天的日期作为列名：

DECLARE @Today DATE = CAST(GETDATE() AS DATE)
DECLARE @DynamicCols NVARCHAR(MAX)
DECLARE @UnpivotCols NVARCHAR(MAX)
DECLARE @SQL NVARCHAR(MAX)

-- 生成最近7天的日期列表，构造列名和UNPIVOT所需参数
WITH DateRange AS (
    SELECT @Today AS DateVal
    UNION ALL
    SELECT DATEADD(DAY, -1, DateVal) FROM DateRange WHERE DateVal > DATEADD(DAY, -6, @Today)
)
SELECT
    @DynamicCols = STRING_AGG(QUOTENAME(CONVERT(VARCHAR(10), DateVal, 23)) + ' = SUM(CASE WHEN CAST(createdTimestamp AS DATE) = ''' + CONVERT(VARCHAR(10), DateVal, 23) + ''' THEN 1 ELSE 0 END)', ', '),
    @UnpivotCols = STRING_AGG(QUOTENAME(CONVERT(VARCHAR(10), DateVal, 23)), ', ')
FROM DateRange

-- 拼接并执行动态SQL
SET @SQL = '
SELECT
    SUMTYPE AS [日期],
    SUMTOTAL AS [销售总数]
FROM (
    SELECT
        ' + @DynamicCols + '
    FROM sales
    WHERE CAST(createdTimestamp AS DATE) BETWEEN ''' + CONVERT(VARCHAR(10), DATEADD(DAY, -6, @Today), 23) + ''' AND ''' + CONVERT(VARCHAR(10), @Today, 23) + '''
) AS PivotData
UNPIVOT (
    SUMTOTAL FOR SUMTYPE IN (' + @UnpivotCols + ')
) AS UnpvtData
ORDER BY SUMTYPE DESC;'

EXEC sp_executesql @SQL

关键说明 #

• 原查询多次独立查询sales表，性能较差，两种方案都优化为单次扫描表后聚合计算。
• 动态SQL方案中，用STRING_AGG（适用于SQL Server 2017及以上版本）自动生成日期列名，无需硬编码Day1-Day7。
• 日期格式用CONVERT(VARCHAR(10), DateVal, 23)生成YYYY-MM-DD格式，确保列名清晰规范。

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