MySQL 8.0

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
CREATE TABLE mytable (
  ID INT,  
  snapdate DATE,
  createdate DATE
);

INSERT INTO mytable VALUES
(123, '2022-10-03', '2022-12-29'),
(123, '2022-10-10', '2022-02-01'),
(246, '2022-10-03', '2022-02-04'),
(246, '2022-10-10', '2022-01-11');

Records: 4  Duplicates: 0  Warnings: 0


SELECT ID,
       MAX(CASE WHEN snapdate='2022-10-03' THEN createdate END) AS '2022-10-03',
       MAX(CASE WHEN snapdate='2022-10-10' THEN createdate END) AS '2022-10-10'
FROM mytable
GROUP BY ID


ID
2022-10-03
2022-10-10


123
2022-12-29
2022-02-01

246
2022-02-04
2022-01-11


SET @columns := NULL; /*for the columns*/
SET @sql := NULL; /*for the final query*/

SELECT GROUP_CONCAT(
       DISTINCT CONCAT("MAX(CASE WHEN snapdate='", snapdate,"' THEN createdate END) AS '",snapdate,"'")
        ORDER BY snapdate) INTO @columns
FROM mytable;

SELECT CONCAT("SELECT ID,",@columns," 
     FROM mytable
     GROUP BY ID") INTO @sql;

PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

Statement prepared




ID
2022-10-03
2022-10-10


123
2022-12-29
2022-02-01

246
2022-02-04
2022-01-11


SELECT @columns;
SELECT @sql;


@columns


MAX(CASE WHEN snapdate='2022-10-03' THEN createdate END) AS '2022-10-03',MAX(CASE WHEN snapdate='2022-10-10' THEN createdate END) AS '2022-10-10'




@sql


SELECT ID,MAX(CASE WHEN snapdate='2022-10-03' THEN createdate END) AS '2022-10-03',MAX(CASE WHEN snapdate='2022-10-10' THEN createdate END) AS '2022-10-10' 
     FROM mytable
     GROUP BY ID


SET @sql := NULL;

SELECT CONCAT("SELECT ID,",GROUP_CONCAT(
       DISTINCT CONCAT("MAX(CASE WHEN snapdate='", snapdate,"' THEN createdate END) AS '",snapdate,"'")
        ORDER BY snapdate)," 
     FROM mytable
     GROUP BY ID") INTO @sql
FROM mytable;

PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

Statement prepared




ID
2022-10-03
2022-10-10


123
2022-12-29
2022-02-01

246
2022-02-04
2022-01-11


INSERT INTO mytable VALUES
(123, '2022-10-22', '2022-12-29'),
(123, '2022-10-30', '2022-02-01'),
(246, '2022-10-22', '2022-02-04'),
(246, '2022-10-30', '2022-01-11');

Records: 4  Duplicates: 0  Warnings: 0


SET @sql := NULL;

SELECT CONCAT("SELECT ID,",GROUP_CONCAT(
       DISTINCT CONCAT("MAX(CASE WHEN snapdate='", snapdate,"' THEN createdate END) AS '",snapdate,"'")
        ORDER BY snapdate)," 
     FROM mytable
     GROUP BY ID") INTO @sql
FROM mytable;

PREPARE stmt FROM @sql;
EXECUTE stmt;
DEALLOCATE PREPARE stmt;

Statement prepared




ID
2022-10-03
2022-10-10
2022-10-22
2022-10-30


123
2022-12-29
2022-02-01
2022-12-29
2022-02-01

246
2022-02-04
2022-01-11
2022-02-04
2022-01-11
ID	2022-10-03	2022-10-10	2022-10-22	2022-10-30
123	2022-12-29	2022-02-01	2022-12-29	2022-02-01
246	2022-02-04	2022-01-11	2022-02-04	2022-01-11