Quick suggestion

😎

;WITH FIRST_NAME_DATA AS

(

SELECT

FN.C_ID

,FN.C01

FROM dbo.TestTbl1 TT

CROSS APPLY

(

SELECT 1, col1 UNION ALL

SELECT 2, col2 UNION ALL

SELECT 3, col3 UNION ALL

SELECT 4, col4

) FN(C_ID,C01)

WHERE TT.Name = 'FirstName'

)

,LAST_NAME_DATA AS

(

SELECT

FN.C_ID

,FN.C01

FROM dbo.TestTbl1 TT

CROSS APPLY

(

SELECT 1, col1 UNION ALL

SELECT 2, col2 UNION ALL

SELECT 3, col3 UNION ALL

SELECT 4, col4

) FN(C_ID,C01)

WHERE TT.Name = 'LastName'

)

SELECT

FND.C01 AS FirstName

,LND.C01 AS LastName

FROM FIRST_NAME_DATA FND

INNER JOIN LAST_NAME_DATA LND

ON FND.C_ID = LND.C_ID;

Output

FirstName LastName

----------- -----------

1 11

2 22

3 33

4 44