SQL Server 2017

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.

id	relevant_data_01	relevant_data_02	20200101	20200102	20200103
1	a	b	1	2	3
1	a	b	1	2	3

DECLARE 
  @sql  NVARCHAR(MAX) = '',
  @cols NVARCHAR(MAX) = '';

SELECT @cols += ', ' + QUOTENAME(name)
  FROM sys.columns
  WHERE [object_id] = OBJECT_ID('Dummy')
  AND name LIKE '2020%';

--setting the dummy values for @cols
  --Set @cols = ', [20200101], [20200102]'

SET @cols =  STUFF(@cols, 1, 1, '')

-- print @cols

-- print '---------------'
  
SELECT @sql = N'SELECT id, relevant_data_01, relevant_data_02, date_columns, val
  FROM 
  (
    SELECT id, relevant_data_01, relevant_data_02, '  + @cols + '
    FROM Dummy
  ) t
  UNPIVOT
  (
    val FOR date_columns IN (' + @cols + ')
  ) up;';

-- print @sql
-- EXEC sp_executesql @sql;

---final results into temp table

SELECT @sql = N'SELECT id, relevant_data_01, relevant_data_02, date_columns, val
into #temptable
  FROM 
  (
    SELECT id, relevant_data_01, relevant_data_02, '  + @cols + '
    FROM Dummy
  ) t
  UNPIVOT
  (
    val FOR date_columns IN (' + @cols + ')
  ) up;
  
  select * from #temptable where id = 1
  ';

-- print @sql
EXEC sp_executesql @sql;

id	relevant_data_01	relevant_data_02	date_columns	val
1	a	b	20200101	1
1	a	b	20200102	2
1	a	b	20200103	3
1	a	b	20200101	1
1	a	b	20200102	2
1	a	b	20200103	3