Static and Dynamic Inner Join Pivot Query, Sql Server

Language: Layout:

IF OBJECT_ID('dbo.ColumnNames', 'U') IS NOT NULL 
  DROP TABLE dbo.ColumnNames; 
  
CREATE TABLE ColumnNames (
    [ColumnNameID] INT,
    [Name] VARCHAR(30),
    [Type] VARCHAR(10),
    [Status] VARCHAR(10)
);

INSERT INTO ColumnNames VALUES (1, 'Fullname', 'varchar', 'Active');
INSERT INTO ColumnNames VALUES (2, 'Email', 'varchar', 'Active');
INSERT INTO ColumnNames VALUES (3, 'Position', 'varchar', 'Active');
INSERT INTO ColumnNames VALUES (4, 'Category', 'varchar', 'Active');

IF OBJECT_ID('dbo.ColumnValues', 'U') IS NOT NULL 
  DROP TABLE dbo.ColumnValues; 
  
CREATE TABLE ColumnValues (
    ID INT,
    [ColumnNameID] INT,
    [ColumnValue] VARCHAR(50),
    [Status] VARCHAR(50)
);

INSERT INTO ColumnValues VALUES  (1, 1, 'Linda', 'Active');
INSERT INTO ColumnValues VALUES  (2, 2, 'linda@email.com', 'Active');
INSERT INTO ColumnValues VALUES  (3, 3, 'Soft Eng.',  'Active');  
INSERT INTO ColumnValues VALUES  (4, 4, 'Cat 1',  'Active');
INSERT INTO ColumnValues VALUES  (5, 1, 'Remil',  'Active');
INSERT INTO ColumnValues VALUES  (6, 2, 'Remil@email.com', 'Active');
INSERT INTO ColumnValues VALUES  (7, 3, 'Senior Soft Eng.', 'Active');  
INSERT INTO ColumnValues VALUES  (8, 4, 'Cat 2',  'Active');
INSERT INTO ColumnValues VALUES  (9, 1, 'Ash',  'Active');
INSERT INTO ColumnValues VALUES  (10, 2, 'ash@email.com', 'Active');
INSERT INTO ColumnValues VALUES  (11, 3, 'Soft Eng.',  'Active');
INSERT INTO ColumnValues VALUES  (12, 4, 'Cat 1',  'Active');

SELECT * FROM ColumnNames;

SELECT * FROM ColumnValues;
                      
                      
--- STATIC QUERY
                          
SELECT [Fullname], [Email], [Position], [Category]
FROM (
    SELECT i.ColumnValue, a.[Name],
           ROW_NUMBER() OVER (PARTITION BY a.[Name] ORDER BY a.ColumnNameID) AS rn
    FROM ColumnValues i
    INNER JOIN ColumnNames a
      ON i.ColumnNameID = a.ColumnNameID
  ) tbl

PIVOT
   (MAX(ColumnValue) 
    FOR [Name] IN ([Fullname], [Email], [Position], [Category])
   ) pvt
ORDER BY rn

--- DYNAMIC QUERY

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

SET @cols = STUFF((SELECT distinct ',' + QUOTENAME(c.[Name]) 
            FROM ColumnNames c
            FOR XML PATH(''), TYPE
            ).value('.', 'NVARCHAR(MAX)') 
             ,1,1,'')
        
        
        
SET @query = 'SELECT ' + @cols + '
                FROM (
                    SELECT i.ColumnValue, a.[Name],
                           ROW_NUMBER() OVER (PARTITION BY a.[Name] ORDER BY a.ColumnNameID) AS rn
                    FROM ColumnValues i
                    INNER JOIN ColumnNames a
                      ON i.ColumnNameID = a.ColumnNameID
                  ) tbl

PIVOT
                   (MAX(ColumnValue) 
                    FOR [Name] IN (' + @cols + ')
                   ) pvt
                ORDER BY rn'

EXECUTE(@query)

View schema
Execution time: 0,05 sec, rows selected: 24, rows affected: 16, absolute service time: 0,2 sec, absolute service time: 0,2 sec