SQL Server : pivot columns

Question

I have a table like this:

id	EVT	DATE
1	M1	2022-01-01
1	M2	NULL
1	M3	2023-01-01
2	M7	2024-01-01
2	M9	2025-01-01

i want a table like this:

id	M1	M2	M3	M7	M9
1	2022-01-01	NULL	2023-01-01	NULL	NULL
2	NULL	NULL	NULL	2024-01-01	2025-01-01

Do you have a SQL query suggestions to make this transformation ?

I tried :

select ID, M1, M2, M3, M7, M9
from Table
pivot (MAX(DATE) FOR EVT in (M1, M2, M3, M7, M9) AS P

I got EVT pivoted but a row for each date

id	M1	M2	M3	M7	M9
1	2022-01-01	NULL	NULL	NULL	NULL
1	NULL	NULL	NULL	NULL	NULL
1	NULL	NULL	2023-01-01	NULL	NULL

Answer 1

Try this:

DROP TABLE IF EXISTS #DataSource;

CREATE TABLE #DataSource
(
    [id] INT,
    [EVT] VARCHAR(10),
    [DATE] DATE
);

INSERT INTO #DataSource ([id], [EVT], [DATE])
VALUES (1, 'M1', '2022-01-01'),
       (1, 'M2', NULL),
       (1, 'M3', '2023-01-01'),
       (2, 'M7', '2024-01-01'),
       (2, 'M9', '2025-01-01');

DECLARE @DynamicTSQLStatement NVARCHAR(MAX),
        @DynamicPIVOTColumns NVARCHAR(MAX);

SELECT @DynamicPIVOTColumns = STRING_AGG(CAST(QUOTENAME([EVT]) AS NVARCHAR(MAX)), ',') WITHIN GROUP (ORDER BY [EVT])
FROM
(
    SELECT DISTINCT [EVT]
    FROM #DataSource
   
) DS ([EVT]);

SET @DynamicTSQLStatement = N'
SELECT *
FROM #DataSource
PIVOT
(
    MAX([DATE]) FOR [EVT] IN (' + @DynamicPIVOTColumns + ')
) PVT';

EXEC sp_executesql @DynamicTSQLStatement;

DROP TABLE #DataSource;

You can use the following with STRING_AGG is not supported:

SET @DynamicPIVOTColumns = STUFF
                          (
                                (
                                SELECT ',[' + CAST([EVT] AS VARCHAR(12)) + ']'
                                FROM #DataSource
                                GROUP BY [EVT]
                                ORDER BY [EVT]
                                FOR XML PATH('') ,TYPE
                                ).value('.', 'NVARCHAR(MAX)')
                                ,1
                                ,1
                                ,''
                          );