SQL Server 2022

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

SELECT * 
INTO MyTable
FROM
    (SELECT
         'C1' AS [c_id], '1' AS [a1], '2' AS [a2], '1.1' AS [a3], '1.4' AS [a4]
     UNION All
     SELECT 'C2' AS [c_id], 'Ann' AS [a1], 'Jane' AS [a2], 'Andrew' AS [a3], 'John' AS [a4]
     UNION All
     SELECT 'C3' AS [c_id], '24323' AS [a1], '4323' AS [a2], '5542' AS [a3], '45543' AS [a4]
) r;

CREATE TABLE #tempTable
(
    [Transpose] NVARCHAR(128),
    C1 NVARCHAR(MAX),
    C2 NVARCHAR(MAX),
    C3 NVARCHAR(MAX)
)

DECLARE @cols AS NVARCHAR(MAX),
        @query AS NVARCHAR(MAX),
        @crossApplyCols AS NVARCHAR(MAX)

SELECT @cols = STUFF((SELECT DISTINCT ',' + QUOTENAME(C_id)
                      FROM MyTable
                      FOR XML PATH(''), TYPE).value('.', 'NVARCHAR(MAX)'), 1, 1, '')

SELECT @crossApplyCols = STUFF((SELECT ',' + CHAR(10) + CHAR(9) + '(''' + c.name + ''', ' + QUOTENAME(c.name) + ')'
                                FROM sys.columns c
                                WHERE c.object_id = OBJECT_ID('MyTable') AND c.name <> 'c_id'
                                FOR XML PATH(''), TYPE).value('.', 'NVARCHAR(MAX)'), 1, 1, '')

SET @query = 'INSERT INTO #tempTable
              SELECT [Transpose], ' + @cols + '
              FROM
              (
                  SELECT C_id, a AS [Transpose], value
                  FROM MyTable
                  CROSS APPLY
                  (
                      VALUES
                          ' + @crossApplyCols + '
                  ) AS ca (a, value)
              ) AS src
              PIVOT
              (
                  MAX(value) FOR C_id IN (' + @cols + ')
              ) AS pvt'

EXEC sp_executesql @query

-- Access the temp table data outside the dynamic SQL
SELECT * FROM #tempTable

Transpose	C1	C2	C3
a1	1	Ann	24323
a2	2	Jane	4323
a3	1.1	Andrew	5542
a4	1.4	John	45543