对于包含数据的每一行，每个类别都需要一行

阅读戈登的答案后，这就是我的想法。我逐步创建了它，以便可以看到发生了什么。我创建的日期不包含master..spt_values表。我还创建了一个临时人员表，因此我可以只选择具有TimeLogRecord的人员，然后重新使用它为最终选择输入详细信息。让我知道是否有任何方法可以使运行速度更快。

DECLARE 
    @startDate  DATE,
    @endDate    DATE

SET @startDate = '2014-01-01'
SET @endDate =   '2014-01-31'

-- create day rows --
;WITH dates(TimeLogDay) AS 
(
    SELECT @startDate AS TimeLogDay
    UNION ALL
    SELECT DATEADD(d, 1, TimeLogDay)
    FROM dates 
    WHERE TimeLogDay < @enddate
)

-- create a type row for each day --
SELECT
    dates.TimeLogDay,
    tlt.TimeLogTypeID

INTO #TypeDate

FROM 
    dates CROSS JOIN 
    (SELECT
        TimeLogType.TimeLogTypeID
     FROM 
        TimeLogType 
     WHERE 
        ISNULL(TimeLogType.DeletedInd, 0) = 0
    ) AS TLT 

-- create a temp person table for referance later ---
SELECT * INTO #person FROM Person WHERE Person.personID IN 
        (SELECT Timelog.PersonID FROM TimeLog WHERE TimeLog.StartDateTime BETWEEN @startDate AND @endDate)

-- sum up the log times and tie in the date/type rows --
SELECT  
    #TypeDate.TimeLogDay,
    #TypeDate.TimeLogTypeID,
    #person.PersonID,
    SUM(dbo.fnCalculateHoursAsDecimal(TimeLog.StartDateTime, TimeLog.EndDateTime)) AS Hours

INTO #Hours

FROM
    #person CROSS JOIN
    #TypeDate LEFT JOIN
    TimeLog ON
        TimeLog.PersonID = #person.PersonID AND
        TimeLog.TimeLogTypeID = #TypeDate.TimeLogTypeID AND
        #TypeDate.TimeLogDay = CONVERT(DATE, TimeLog.StartDateTime, 101)

GROUP BY
    #TypeDate.TimeLogDay,
    #TypeDate.TimeLogTypeID,
    #person.PersonID

-- now tie in the details to complete --
SELECT
    #Hours.TimeLogDay,
    TimeLogType.Description,
    Person.LastName,
    Person.FirstName,
    #Hours.Hours

FROM
    #Hours LEFT JOIN
    Person ON #Hours.PersonID = Person.PersonID LEFT JOIN
    TimeLogType ON #Hours.TimeLogTypeID = TimeLogType.TimeLogTypeID

ORDER BY
    Person.FirstName,
    Person.LastName,
    #Hours.TimeLogDay,
    TimeLogType.SortOrder