Click here to Skip to main content
15,884,472 members
Please Sign up or sign in to vote.
5.00/5 (1 vote)
See more:

I need a help to update the row values of one table(ColumnNames) into another tables(ColumnValues) column name.

Here are my two tables,

ColumnValues table columns as Column1,Column2,Column3,Column4,Column5 and ColumnNames table column as ColumnNo,ColumnName

Here the ColumnNames tables row values will be updated as column name of ColumnValues table.

For example, ColumnNames row values like Name,Email,Qualification,etc., Here The column name of ColumnValues will updated as Column1 as Name,Column2 as Email and Column 3 as Qualification etc.,

Could you please help me to achieve on this?

Thanks in Advance.

Arunkumar Murugesan

What I have tried:

I have tried some Pivot options since I'm new to Pivot concept I couldn't achieve this scenario.
Updated 16-Nov-21 1:49am

1 solution

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
The query to populate your ColumnValues table would then be
insert into @ColumnValues (Column1,Column2,Column3,Column4,Column5) 
select [1],[2],[3],[4],[5]
	select ColumnNo, ColumnName
	from @ColumnNames
) src
	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

select random, [1],[2],[3],[4],[5]
	select ColumnNo, ColumnName, random
	from @ColumnNames
) src
	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, '')  
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
Share this answer
Arunkumar Murugesan 16-Nov-21 8:09am    
@CHill60 Thank you so much for the Update.

In your solution the column name filled as row values of Column1,Colum2 etc.,

But my request here is the Column name will be Name,Email,Qualification instead of COlumn1,Column2,Column3....

Note :
a.) I have a multiple column names from one table(ColumnValues) like Column1,Column2,Column3.......Column100 etc.,
b.) And I have column names for the each columns in another table(ColumnNames)
c.) Now I want to update the first table(ColumnValues) column names using second table(Columnvalues) rows

For example,

ColumnValues Table as,

Column1 Column2 Column3 Column4 Column5
AAA B.Sc Testing 1000
BBB M.Sc Developer 2000
CCC BE Architect 2000

And ColumnNames table value as,

ColumnNo ColumnName
1 Name
2 Email
3 Qualification
4 Role
5 Salary

here I need a output as,

Name Email Qualification Role Salary
AAA B.Sc Testing 1000
BBB M.Sc Developer 2000
CCC BE Architect 2000
CHill60 16-Nov-21 9:25am    
I have updated my solution - see the "EDIT after OP Comment" section
Arunkumar Murugesan 16-Nov-21 23:56pm    
@Chill60.. Thank you so much for your effort. Your solution works for me. Thanks a lot.
CHill60 17-Nov-21 4:21am    
My pleasure! Sorry I got the wrong end of the stick first time around
Maciej Los 16-Nov-21 12:36pm    

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900