SQL Server 2022

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
If(OBJECT_ID('tempdb..#candidates') Is Not Null) Drop Table #candidates
CREATE TABLE #candidates
(LAST_NAME  VARCHAR(128),FIRST_NAME VARCHAR(128),MIDDLE_NAME VARCHAR(128))

INSERT INTO #candidates
Values ('Doe', 'John', 'J'), ('Doe', 'Jane', 'S')

SELECT * FROM #candidates



LAST_NAME
FIRST_NAME
MIDDLE_NAME


Doe
John
J

Doe
Jane
S


SELECT
      A.[id]
    , B.[Key]
    , B.[Value]
FROM (
  SELECT *
       , row_number() over(order by LAST_NAME, FIRST_NAME, MIDDLE_NAME) as id
  FROM #candidates
  ) A
CROSS APPLY (
    SELECT *
    FROM OpenJson((
                /* list out the columns you need to be unpivoted into keys here */
                SELECT
                      A.LAST_NAME
                    , A.FIRST_NAME
                    , A.MIDDLE_NAME
                FOR JSON Path
                    , Without_Array_Wrapper
                ))
    ) B


id
Key
Value


1
LAST_NAME
Doe

1
FIRST_NAME
Jane

1
MIDDLE_NAME
S

2
LAST_NAME
Doe

2
FIRST_NAME
John

2
MIDDLE_NAME
J


DECLARE @columns NVARCHAR(MAX)
DECLARE @sql NVARCHAR(MAX)

-- Generate the dynamic @columns for the pivot operation
SET @columns = (
    SELECT STRING_AGG(QUOTENAME(id), ', ')
    FROM (
        SELECT ROW_NUMBER() OVER (ORDER BY LAST_NAME, FIRST_NAME, MIDDLE_NAME) AS id
        FROM #candidates
    ) AS ids
)

  -- Generate the final dynamic SQL for the pivot operation
SET @sql = N'
SELECT
    [Key], ' + @columns + '
FROM (
    SELECT
          A.[id]
        , B.[Key]
        , B.[Value]
    FROM (
      SELECT *
           , row_number() over(order by LAST_NAME, FIRST_NAME, MIDDLE_NAME) as id
      FROM #candidates
      ) A
    CROSS APPLY (
        SELECT *
        FROM OpenJson((
                    /* list out the columns you need to be unpivoted into keys here */
                    SELECT
                          A.LAST_NAME
                        , A.FIRST_NAME
                        , A.MIDDLE_NAME
                    FOR JSON Path
                        , Without_Array_Wrapper
                    ))
        ) B
     ) AS src
PIVOT (
    MAX(Value)
    FOR id IN (' + @columns + ')
    ) AS piv;
'

-- SELECT @sql;  -- optional, inspect the generated SQL

-- Execute the final dynamic SQL with the pivot operation
EXEC sp_executesql @sql;


Key
1
2


FIRST_NAME
Jane
John

LAST_NAME
Doe
Doe

MIDDLE_NAME
S
J
id	Key	Value
1	LAST_NAME	Doe
1	FIRST_NAME	Jane
1	MIDDLE_NAME	S
2	LAST_NAME	Doe
2	FIRST_NAME	John
2	MIDDLE_NAME	J