SQL Server需实现以日期为列的动态数据透视表

要实现把日期转为列的动态透视表，核心是用动态SQL处理可变的日期范围（因为你的日期区间是通过Coldfusion的#Startdate#和#Enddate#传入的，静态Pivot没法适配）。以下是具体实现步骤：

1. 明确透视核心逻辑 #

按人员维度分组，将指定日期范围内的每个日期转为列，展示对应日期的排班关键信息（比如班次、工时、工作单元等），按需调整展示内容。

2. 完整动态SQL代码 #

DECLARE @StartDate DATE = #Startdate#;
DECLARE @EndDate DATE = #Enddate#;
DECLARE @PivotColumns NVARCHAR(MAX);
DECLARE @DynamicSQL NVARCHAR(MAX);

-- 生成日期范围内所有需作为列的日期（转成字符串格式）
WITH DateRange AS (
    SELECT @StartDate AS DateVal
    UNION ALL
    SELECT DATEADD(DAY, 1, DateVal)
    FROM DateRange
    WHERE DateVal < @EndDate
)
SELECT @PivotColumns = STRING_AGG(QUOTENAME(CONVERT(NVARCHAR(10), DateVal, 23)), ', ')
FROM DateRange;

-- 拼接动态透视SQL
SET @DynamicSQL = N'
WITH SchedulingData AS (
    SELECT 
        e.fullname,
        e.FirstName,
        e.LastInit,
        ec.position,
        ec.user_id,
        CONVERT(NVARCHAR(10), ec.Days_date, 23) AS DateStr,
        -- 自定义单元格展示内容，按需调整
        CONCAT(ec.Shift, ''班 '', ec.UnitWorking, '' '', ec.HoursWorked, ''h'', CASE WHEN ec.Comments <> '''' THEN '' ('' + ec.Comments + '')'' ELSE '''' END) AS ShiftDetail
    FROM EmpCalendar ec
    INNER JOIN Employees e ON ec.user_id = e.user_id
    WHERE 
        (LEN(ec.Off_type) < 1 OR ec.Off_type IS NULL)
        AND ec.Days_date BETWEEN @StartDate AND @EndDate
        AND ec.Shift = ''1''
)
SELECT 
    fullname,
    FirstName,
    LastInit,
    position,
    user_id,
    ' + @PivotColumns + '
FROM SchedulingData
PIVOT (
    MAX(ShiftDetail)
    FOR DateStr IN (' + @PivotColumns + ')
) AS PivotTable
ORDER BY fullname;';

-- 执行动态SQL
EXEC sp_executesql @DynamicSQL, N'@StartDate DATE, @EndDate DATE', @StartDate, @EndDate;

关键细节说明 #

• 日期类型处理：用CONVERT(NVARCHAR(10), Days_date, 23)把DATE类型转成YYYY-MM-DD格式的字符串，解决Pivot不支持DATE类型作为列名的问题。
• 动态列生成：用STRING_AGG（SQL Server 2017+支持）自动拼接所有日期列名，旧版本可改用FOR XML PATH方式拼接。
• 单元格内容自定义：ShiftDetail字段可根据需求调整，比如只保留工时ec.HoursWorked，或单独展示班次ec.Shift。
• Coldfusion变量适配：直接将#Startdate#和#Enddate#替换为Coldfusion的日期变量即可，确保传入格式为合法DATE类型。

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