SQL使用Pivot生成动态列：如何按日期而非站点ID实现数据透视

以下是SQL Server环境下的动态透视实现代码，可按照日期自动生成动态列，适配你提到的最多5个不同日期的场景：

-- 1. 定义变量存储动态拼接的列名、动态SQL语句
DECLARE @cols AS NVARCHAR(MAX), @query AS NVARCHAR(MAX)

-- 2. 提取去重的日期，拼接为透视需要的[日期值]格式，此处可根据需求调整日期格式，比如转成yyyy-MM-dd的日期类型
SELECT @cols = STUFF((
    SELECT DISTINCT ',' + QUOTENAME(CONVERT(DATE, CreatedDate)) 
    -- 如果你需要保留完整时间作为列名，替换上面一行为：
    -- SELECT DISTINCT ',' + QUOTENAME(CreatedDate)
    FROM (
        SELECT d.CreatedDate
        FROM DWCorp.SSMaster m 
        INNER JOIN DWCorp.SSDetail d ON d.MasterId = m.Id 
        WHERE ActionId = 7
    ) AS t
    ORDER BY ',' + QUOTENAME(CONVERT(DATE, CreatedDate)) -- 按日期排序列顺序
    FOR XML PATH(''), TYPE
).value('.', 'NVARCHAR(MAX)'), 1, 1, '')

-- 3. 拼接完整的动态透视查询语句
SET @query = '
SELECT ProjectNum, siteid, ' + @cols + '
FROM (
    SELECT 
        CONVERT(DATE, d.CreatedDate) AS CreatedDate, -- 和前面的日期格式保持一致
        -- 保留完整时间则改为：d.CreatedDate AS CreatedDate
        m.siteid,
        m.ProjectNum
    FROM DWCorp.SSMaster m 
    INNER JOIN DWCorp.SSDetail d ON d.MasterId = m.Id 
    WHERE ActionId = 7
) AS Sourcetable
PIVOT (
    COUNT(CreatedDate) -- 聚合函数可根据你的业务需求调整，这里统计每个项目+站点在对应日期的记录数
    FOR CreatedDate IN (' + @cols + ')
) AS pivottable 
ORDER BY siteid, ProjectNum'

-- 4. 执行动态SQL
EXEC sp_executesql @query

• 代码中的聚合函数可以根据你的实际业务调整，如果需要取最大/最小日期替换COUNT(CreatedDate)为MAX(CreatedDate)或者MIN(CreatedDate)即可。
• 如果希望列名显示为「日期1」「日期2」而非具体日期值，可以调整列名拼接逻辑，按日期排序后拼接别名即可。

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