我们最近将一些表从access转换为sqlserver，我想将这个access交叉表查询复制到sqlserver

TRANSFORM First(IIf([FieldName]="Engagement",IIf([Engagement]=1,"Yes","No"),IIf([Interactive]=1,"Yes",IIf([Interactive]=0,"No","N/A")))) AS TheValue
SELECT tblStudent.Surname, tblStudent.Forename
FROM tblXtabColumns, tblStudent INNER JOIN tblEngagement ON tblStudent.SID = tblEngagement.SID
WHERE (((tblEngagement.Class)=[Class?]))
GROUP BY tblStudent.Surname, tblStudent.Forename
PIVOT [FieldName] & " " & Day([AttendTime]) & " " & MonthName(Month([AttendTime]),True);

表tblengement在数据库中类似于

SID         AttendTime          Engagement  Interactive
12345       01/01/2020 14:00    1           1
56789       01/01/2020 14:00    1           0
12345       07/01/2020 14:00    1           1
56789       07/01/2020 14:00    1           1
12345       14/01/2020 14:00    1           0
56789       14/01/2020 14:00    1           1

我希望pivot查询产生如下结果

SID   Engagement 1 Jan  Interactive 1 Jan   Engagement 7 Jan    Interactive 7 Jan   Engagement 14 Jan   Interactive 14 Jan
12345 Yes               Yes                 Yes                 Yes                 Yes             No
56789 Yes               No                  Yes                 Yes                 Yes            Yes

我已经成功地编写了一个sql server查询，其中只包含了engagement列，如下所示：

DECLARE @Lesson varchar(2000)
    DECLARE @Query varchar(4000)
    SELECT @Lesson = STUFF(( SELECT DISTINCT 
                            '],[' + CONVERT(varchar, AttendTime) 
                            FROM tblEngagement 
                            ORDER BY '],[' + CONVERT(varchar, AttendTime)  
                            FOR XML PATH('')), 1, 2, '') + ']'

    SET @Query =
    'SELECT * 
    FROM 
        ( SELECT SID, AttendTime, Engagement 
            FROM tblEngagement  
        ) ps
    PIVOT
        ( MAX(Engagement)
            FOR AttendTime IN ('+ @Lesson +')
        ) AS pvt1
    '

    EXECUTE (@Query)

这给了我这个

SID Jan 1 2020  Jan 7 2020
12345   1       0
56789   0       1

我试过做以下几件事

DECLARE @Lesson varchar(2000)
DECLARE @Query varchar(4000)
SELECT @Lesson = STUFF(( SELECT DISTINCT 
                        '],[' + CONVERT(varchar, AttendTime) 
                        FROM tblEngagement 
                        ORDER BY '],[' + CONVERT(varchar, AttendTime)  
                        FOR XML PATH('')), 1, 2, '') + ']'

SET @Query =
'SELECT * 
FROM 
    ( SELECT SID, AttendTime, Engagement, Interactive 
        FROM tblEngagement  
    ) ps
PIVOT
    ( MAX(Engagement)
        FOR AttendTime IN ('+ @Lesson +')
    ) AS pvt1
PIVOT
    ( MAX(Interactive)
        FOR AttendTime IN ('+ @Lesson +')
    ) AS pvt2
'
EXECUTE (@Query)

但这给了我错误：

Msg 207, Level 16, State 1, Line 15
Invalid column name 'AttendTime'.
Msg 265, Level 16, State 1, Line 15
The column name "Jan  1 2020  2:00PM" specified in the PIVOT operator conflicts with the existing column name in the PIVOT argument.
Msg 265, Level 16, State 1, Line 15
The column name "Jul 7 2020  2:00PM" specified in the PIVOT operator conflicts with the existing column name in the PIVOT argument.
Msg 8156, Level 16, State 1, Line 16
The column 'Jan  1 2020  2:00PM' was specified multiple times for 'pvt2'

只是要指出，更多的数据是每周添加，希望这是有意义的。