SQL Server 2019

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
--Create sample table
CREATE TABLE dbo.YourTable (id1 int,
                            id2 int,
                            id3 int,
                            value1 char(3),
                            value2 char(3));
--Insert sample data
INSERT INTO dbo.YourTable
VALUES (1,1,1,'x11','x12'),
       (1,1,2,'x21','x22'),
       (2,1,1,'x31','x32'),
       (2,1,2,'x41','x42'),
       (2,1,3,'x51','x52');
5 rows affected

--Solution
DECLARE @SQL nvarchar(MAX),
        @CRLF nchar(2) = NCHAR(13) + NCHAR(10);

DECLARE @Delim nchar(3) = ',' + @CRLF;

SET @SQL = N'SELECT YT.id1,' + @CRLF + 
           N'       YT.id2,' + @CRLF + 
           (SELECT STRING_AGG(N'       MAX(CASE id3 WHEN ' + QUOTENAME(yt.id3,'''') + N' THEN YT.' + QUOTENAME(c.[name]) + N' END) AS ' + QUOTENAME(CONCAT(c.name,'_id3=',yt.id3)),@Delim) WITHIN GROUP (ORDER BY yt.id3,c.[name])
            --Note, due to string ordering, value2 will have a GREATER value than value10
            FROM (SELECT DISTINCT id3
                  FROM dbo.YourTable) yt
                 CROSS JOIN (SELECT c.[name]
                             FROM sys.schemas s
                                  JOIN sys.tables t ON s.schema_id = t.schema_id
                                  JOIN sys.columns c ON t.object_id = c.object_id
                             WHERE s.[name] = N'dbo' --This would be your schema
                               AND t.[name] = N'YourTable' --This would be your table
                               AND c.[name] LIKE 'value%') c) + @CRLF +
           N'FROM dbo.YourTable YT' + @CRLF + 
           N'GROUP BY YT.id1, YT.id2;';

PRINT @SQL; --Your best friend
EXEC sys.sp_executesql @SQL;


id1
id2
value1_id3=1
value2_id3=1
value1_id3=2
value2_id3=2
value1_id3=3
value2_id3=3


1
1
x11
x12
x21
x22
null
null

2
1
x31
x32
x41
x42
x51
x52


SELECT YT.id1,
       YT.id2,
       MAX(CASE id3 WHEN '1' THEN YT.[value1] END) AS [value1_id3=1],
       MAX(CASE id3 WHEN '1' THEN YT.[value2] END) AS [value2_id3=1],
       MAX(CASE id3 WHEN '2' THEN YT.[value1] END) AS [value1_id3=2],
       MAX(CASE id3 WHEN '2' THEN YT.[value2] END) AS [value2_id3=2],
       MAX(CASE id3 WHEN '3' THEN YT.[value1] END) AS [value1_id3=3],
       MAX(CASE id3 WHEN '3' THEN YT.[value2] END) AS [value2_id3=3]
FROM dbo.YourTable YT
GROUP BY YT.id1, YT.id2;
Warning: Null value is eliminated by an aggregate or other SET operation.


--Clean up
DROP TABLE dbo.YourTable;