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

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.

Regards,
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.
Posted
Updated 16-Nov-21 1:49am
v2

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
SQL
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
SQL
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
SQL
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
SQL
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
SQL
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
SQL
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
SQL
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
SQL
declare @ssql nvarchar(max);
set @ssql = 'SELECT ' + @colList + ' FROM ColumnValues'
and execute it with
SQL
EXECUTE sp_executesql @ssql
I am intrigued as to any real-world application for this
 
Share this answer
 
v2
Comments
Arunkumar Murugesan 16-Nov-21 8:09am     CRLF
@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 AAA@gmail.com B.Sc Testing 1000 BBB BBB@gmail.com M.Sc Developer 2000 CCC CCC@gmail.com 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 AAA@gmail.com B.Sc Testing 1000 BBB BBB@gmail.com M.Sc Developer 2000 CCC CCC@gmail.com 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    
5ed!

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