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
(
| Transpose | C1 | C2 | C3 |
|---|---|---|---|
| a1 | 1 | Ann | 24323 |
| a2 | 2 | Jane | 4323 |
| a3 | 1.1 | Andrew | 5542 |
| a4 | 1.4 | John | 45543 |