因此，在创建日期数据透视表之前，你可能要做的第一件事就是弄清楚你想要透视哪些日期。为了举例的目的，我刚刚在这个答案中加入了一种方法，你可以在一定范围内获得每个日期，但这实际上取决于你在寻找什么。

SELECT *
FROM (
    SELECT E.EmployeeID, E.EmployeeName, T.DateToCheck, COALESCE(H.val, A.val, L.val, 'Absent') val
    FROM tblEmployee E
    CROSS JOIN (
        SELECT CAST(DATEADD(DAY, number, '2016-09-01') AS DATE)
        FROM master..spt_values
        WHERE type = 'P'
        AND number <= 3) T(DateToCheck)
    LEFT JOIN (SELECT 'Holiday' val, HolidayDate FROM tblHoliday) H ON H.HolidayDate = T.DateToCheck
    LEFT JOIN (SELECT 'Present' val, AttendanceDateTime, EmployeeID FROM tblAttendance) A ON CAST(A.AttendanceDateTime AS DATE) = T.DateToCheck AND A.EmployeeID = E.EmployeeID
    LEFT JOIN (SELECT 'Leave' val, LeaveDateTime, EmployeeID FROM tblLeave) L ON CAST(L.LeaveDateTime AS DATE) = T.DateToCheck AND L.EmployeeID = E.EmployeeID) T
PIVOT (MAX(val) FOR DateToCheck IN ([2016-09-01], [2016-09-02], [2016-09-03], [2016-09-04])) P;

这里的基本逻辑是，您希望生成要检查的日期，将这些日期与每个不同的表进行比较（使用左联接或外部应用），只获得一个结果（此答案中的逻辑使用合并来决定将显示哪个值），然后将结果枢轴旋转。

**编辑：**如果您需要PIVOT作为一组动态列名（例如，动态日期范围），则需要使用动态SQL。有一种方法可以做到：

DECLARE @SQL VARCHAR(MAX) = '', @dateRange VARCHAR(MAX) = '', @startDate DATE = '2016-09-01', @endDate DATE = '2016-09-05';

SELECT @dateRange += ',' + QUOTENAME(DATEADD(DAY, number, @startDate))
FROM master..spt_values 
WHERE type = 'P'
AND DATEADD(DAY, number, @startDate) <= @endDate;

SELECT @dateRange = STUFF(@dateRange, 1, 1, '');

SELECT @SQL = 'SELECT *
FROM (
    SELECT E.EmployeeID, E.EmployeeName, T.DateToCheck, COALESCE(H.val, A.val, L.val, ''Absent'') val
    FROM tblEmployee E
    CROSS JOIN (
        SELECT CAST(DATEADD(DAY, number, ''' + CAST(@startDate AS CHAR(10)) + ''') AS DATE)
        FROM master..spt_values
        WHERE type = ''P''
        AND DATEADD(DAY, number, ''' + CAST(@startDate AS CHAR(10)) + ''') <= ''' + CAST(@endDate AS CHAR(10)) + ''') T(DateToCheck)
    LEFT JOIN (SELECT ''Holiday'' val, HolidayDate FROM tblHoliday) H ON H.HolidayDate = T.DateToCheck
    LEFT JOIN (SELECT ''Present'' val, AttendanceDateTime, EmployeeID FROM tblAttendance) A ON CAST(A.AttendanceDateTime AS DATE) = T.DateToCheck AND A.EmployeeID = E.EmployeeID
    LEFT JOIN (SELECT ''Leave'' val, LeaveDateTime, EmployeeID FROM tblLeave) L ON CAST(L.LeaveDateTime AS DATE) = T.DateToCheck AND L.EmployeeID = E.EmployeeID) T
PIVOT (MAX(val) FOR DateToCheck IN (' + @dateRange + ')) P;';

PRINT @SQL;
EXEC(@SQL);

更改开始和结束日期将更改输出。您可以使用PRINT @SQL查看实际查询。