How to update row names as a column name between two tables in SQL

Your question isn't very clear but I think you want something like this

Here is sample data I could glean from your question - I've used a table variable for now

declare @ColumnValues table (Column1 varchar(20),Column2 varchar(20),Column3 varchar(20),Column4 varchar(20),Column5 varchar(20)) 
declare @ColumnNames table (ColumnNo int,ColumnName varchar(20))
insert into @ColumnNames (ColumnNo, ColumnName) values
(1,'Name'),
(2,'Email'),
(3,'Qualification')

The query to populate your ColumnValues table would then be

insert into @ColumnValues (Column1,Column2,Column3,Column4,Column5) 
select [1],[2],[3],[4],[5]
from
(
	select ColumnNo, ColumnName
	from @ColumnNames
) src
PIVOT
(
	Max(ColumnName) for ColumnNo in ([1],[2],[3],[4],[5])
) pvt

select * from @ColumnValues

Some points to note:

1. This bit is the "source" query

select ColumnNo, ColumnName
from @ColumnNames

It has to be given an alias name and I always like to call mine "src" - short for "source".

2. This bit is what you are going to pivot on

Max(ColumnName) for ColumnNo in ([1],[2],[3],[4],[5])

Again, it has to be given an alias and I always like to call mine "pvt".

3. You MUST use an aggregation inside the pivot. In cases like this, where I really only want the one row that is relevant, or where text is concerned, I always use MAX - it has no real effect on the data, but gets over the need for a aggregation function.

4. Now ... I am using ColumnNo for the actual pivot - i.e. for every distinct value of ColumnNo I want a new column in the results. Each of those results columns will take the name of the value held in ColumnNo. I only have 3 values in my sample, but your question stated up to Column5 so I've allowed up to a maximum of ColumnNo = 5.

5. Because these values are numeric, I have to surround them with [ ] - because they are going to become column names in the results set.

6. This bit is your derived query

select [1],[2],[3],[4],[5]

Note you cannot use ColumnNo nor ColumnName in that part of the query. That may seem strange as they are right there in the src query, but they don't appear as output from the pivot. If you try to use them you will get a "Invalid Column Name" error.

You can include other columns from a table in that final query (as long as they are not used in the pvt part). Here is a subtly different version of your data

declare @ColumnNames table (ColumnNo int,ColumnName varchar(20), random varchar(30))
insert into @ColumnNames (ColumnNo, ColumnName, random) values
(1,'Name','x'),
(2,'Email','x'),
(3,'Qualification','y')

select random, [1],[2],[3],[4],[5]
from
(
	select ColumnNo, ColumnName, random
	from @ColumnNames
) src
PIVOT
(
	Max(ColumnName) for ColumnNo in ([1],[2],[3],[4],[5])
) pvt

which gives results

random	1		2		3				4		5
x		Name	Email	NULL			NULL	NULL
y		NULL	NULL	Qualification		NULL	NULL

EDIT after OP Comment:
You don't need Pivot at all. You want to use Dynamic SQL[^]. You can get the list of column aliases by generating a comma-separated list[^]

E.g. this sql will get you the basis of your select

declare @colList nvarchar(max);
;with cte as
(
	select ColumnNo, 'Column' + cast(ColumnNo as varchar(3)) as genericName, ColumnName 
	from ColumnNames
)
SELECT @colList = STUFF((
    SELECT ', '+ genericName + ' as ' + ColumnName FROM cte ORDER BY ColumnNo FOR XML PATH('')
), 1, 1, '')  
FROM (SELECT 1 AS X) t2

returning

Column1 as Name, Column2 as Email, Column3 as Qualification, Column4 as Role, Column5 as Salary

You can then construct your SQL as

declare @ssql nvarchar(max);
set @ssql = 'SELECT ' + @colList + ' FROM ColumnValues'

and execute it with

EXECUTE sp_executesql @ssql

I am intrigued as to any real-world application for this