PIVOT Query With 2 values and a Date Column

Question

We have recently convert some of our table to SQL Server from Access and I want to reproduce this Access Crosstab query to SQL Server

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);

The table tblEngagement looks like this in the database

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

and I want the pivot query to produce it like this

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

I have managed to get write an SQL Server query with just the Engagement column, here it is:

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)

Which gives me this

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

I have tried doing the following

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)

But this gives me errors:

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'

Just to point out that more data is add every week, hope this makes sense.

Answer 1

Try this:

DROP TABLE IF EXISTS #DataSource;

CREATE TABLE #DataSource
(
    [SID] INT
   ,[AttendTime] DATETIME2
   ,[Engagement] TINYINT
   ,[Interactive] TINYINT
);

INSERT INTO #DataSource ([SID], [AttendTime], [Engagement], [Interactive])
VALUES (12345, '2020/01/01 14:00', 1, 1)
      ,(56789, '2020/01/01 14:00', 1, 0)
      ,(12345, '2020/01/07 14:00', 1, 1)
      ,(56789, '2020/01/07 14:00', 1, 1)
      ,(12345, '2020/01/14 14:00', 1, 0)
      ,(56789, '2020/01/14 14:00', 1, 1);


DECLARE @columns NVARCHAR(MAX);

SELECT @columns = STUFF
(
    (
        SELECT ',' + QUOTENAME([RowValue])
        FROM
        (
            SELECT DENSE_RANK() OVER (ORDER BY [AttendTime]) + 0.1
                  ,CONCAT('Engagement ', DAY([AttendTime]), ' ' ,LEFT(DATENAME(MONTH, MONTH([AttendTime])), 3)) 
            FROM #DataSource
            UNION
            SELECT DENSE_RANK() OVER (ORDER BY [AttendTime]) + 0.2
                  ,CONCAT('Interactive ', DAY([AttendTime]), ' ' ,LEFT(DATENAME(MONTH, MONTH([AttendTime])), 3))
            FROM #DataSource
        ) DS ([RowID], [RowValue])
        ORDER BY [RowID]
        FOR XML PATH(''), TYPE
    ).value('.', 'VARCHAR(MAX)')
    ,1
    ,1
    ,''
);

DECLARE @DanymicSQL NVARCHAR(MAX);

SET @DanymicSQL = N'
SELECT [SID], ' + @columns + '
FROM
(
    SELECT [SID]
          ,CONCAT(''Engagement '', DAY([AttendTime]), '' '' ,LEFT(DATENAME(MONTH, MONTH([AttendTime])), 3))
          ,IIF([Engagement] = 1, ''Yes'', ''No'')
    FROM #DataSource
    UNION ALL
    SELECT [SID]
          ,CONCAT(''Interactive '', DAY([AttendTime]), '' '' ,LEFT(DATENAME(MONTH, MONTH([AttendTime])), 3))
          ,IIF([Interactive] = 1, ''Yes'', ''No'')
    FROM #DataSource
) DS ([SID], [column], [value])
PIVOT
(
    MAX([value]) FOR [column] IN (' + @columns + ')
) PVT';

EXEC sp_executesql @DanymicSQL;