MS SQL Server 2014动态PIVOT未知列报错及结果异常问题排查

以下是对应三个异常点的修复方案： #

动态SQL执行报错修复 #

报错原因是动态SQL执行语法错误，SQL Server中直接使用execute @Query时，会将变量内容识别为存储过程名称查找，因此会抛出“不是有效标识符”的错误。
修复方案：

1. 代码开头补充缺失的变量声明
2. 将最后一行的执行语句替换为正确的动态SQL执行语法

-- 开头补充变量声明
DECLARE @cols NVARCHAR(MAX), @Query NVARCHAR(MAX)

-- 原有逻辑不变
...

-- 替换原有execute @Query为以下写法
EXEC sp_executesql @Query
-- 也可以简写为 EXEC (@Query)

所有用户出勤时长完全相同修复 #

异常原因是SignIn表关联条件缺失参会人ID匹配，当前仅关联了场次ID，导致同一场次的所有签到记录被匹配给该场次所有参会人，聚合后拿到的是全场次的最大签到时长，因此所有用户结果一致。
修复方案：修改SignIn表的JOIN条件，增加参会人ID关联：

LEFT JOIN SignIn s ON (a.Id = s.ActualSessionId AND s.AttendeeId = aa.AttendeeId)

日期格式显示错误修复 #

异常原因是FORMAT函数的日期格式符使用错误，小写mm代表分钟，大写MM才代表月份，之前写的dd-mm-yyyy实际生成的是「日-分钟-年」的格式，不符合需求。
修复方案：将所有出现FORMAT(StartDate, 'dd-mm-yyyy')的位置，格式符替换为dd-MM-yyyy：

1. 生成@cols的部分：FORMAT(StartDate, 'dd-MM-yyyy')
2. 内层子查询的部分：FORMAT(StartDate, ''dd-MM-yyyy'') AS StartDate

完整修正后代码 #

DECLARE @cols NVARCHAR(MAX), @Query NVARCHAR(MAX)

SELECT @cols = STUFF((SELECT DISTINCT '],[' + FORMAT(StartDate, 'dd-MM-yyyy') FROM ActualSession
    FOR XML PATH(''), TYPE
    ).value('.', 'NVARCHAR(MAX)')
    ,1,2,'') + ']'

SET @Query = 'SELECT Code, LastName, FirstName, ' + @cols + ' FROM (
    SELECT i.Code, aa.LastName, aa.FirstName, FORMAT(StartDate, ''dd-MM-yyyy'') AS StartDate, FORMAT(s.SignOut-s.SignIn, ''hh:mm'') AS AttendanceTime
    FROM ActualSession AS a INNER JOIN ActualAttendee aa ON( a.id = aa.ActualSessionId)
    INNER JOIN Attendee att ON (att.Id = aa.AttendeeId) 
    LEFT JOIN SignIn s ON (a.Id = s.ActualSessionId AND s.AttendeeId = aa.AttendeeId) 
    LEFT JOIN Identification i ON (i.AttendeeId = aa.AttendeeId 
        AND i.IdentificationTypeId = (SELECT Id FROM IdentificationType WHERE [Name] = ''Student Code''))
    ) x PIVOT ( max(AttendanceTime) 
    FOR StartDate in (' + @cols + ') ) p '

PRINT @Query  --for debugging

EXEC sp_executesql @Query

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