Rename columns in 1 table to row values in another

Question

Ok, so I have 2 tables in all:

• Table 1 has these 3 columns which are not meaningful as they are just a varchar value:

  Q19243  Q19244  Q19245
  

• Table 2 has 2 columns ColumnName and TextValue.

ColumnName holds the values of the name of the 3 columns in Table 1 (Q19243 etc) and also has a corresponding column called TextValue which holds a friendly description of what Q19243 actually means.

So there are 3 records in Table 2, 1 for each column in Table 1.

I would like to rename these 3 columns in Table 1 to equal whatever is in the TextValue column in Table 2. I would like to do this dynamically rather than a simple UPDATE statement to rename the columns. Sorry I did not attach screen shots but I do not see an attach button to do so...

If you run this code to create an example of the 2 tables then you should probably have a better idea of what I'm referring to.

create table #Table1 (Q19243 varchar(10),Q19244 varchar(10),Q19245 varchar(10))

Create table #Table2 (ColumnName varchar(10),TextValue varchar(50))

Insert into #Table2 select 'Q19243','Provider Name'
Insert into #Table2 select 'Q19244','The Provider You Usually See'
Insert into #Table2 select 'Q19245','How Long Going to Provider'

select * from #Table1
select * from #Table2

drop table #Table1
drop table #Table2

Answer 1

Since the purpose of the column rename is for output purposes only, you can use a query against Table2 to create Dynamic SQL specific to Table1 that aliases the column names on the SELECT.

(the following example uses the sample code in the original question and only differs by what is between the --============== lines)

create table #Table1 (Q19243 varchar(10),Q19244 varchar(10),Q19245 varchar(10))

Create table #Table2 (ColumnName nvarchar(10),TextValue nvarchar(50))

Insert into #Table2 select 'Q19243','Provider Name'
Insert into #Table2 select 'Q19244','The Provider You Usually See'
Insert into #Table2 select 'Q19245','How Long Going to Provider'

select * from #Table1
select * from #Table2

--=========================================
DECLARE @SQL NVARCHAR(MAX)

SELECT @SQL = COALESCE(@SQL + N',', N'SELECT')
               + N' t1.'
               + t2.ColumnName
               + N' AS ['
               + t2.TextValue
               + N']'
FROM #Table2 t2

SET @SQL = @SQL + N' FROM #Table1 t1'

SELECT @SQL

EXEC(@SQL)
--=========================================

drop table #Table1
drop table #Table2

The value of @SQL after the SELECT @SQL= query is:

SELECT t1.Q19243 AS [Provider Name], t1.Q19244 AS [The Provider You Usually See], t1.Q19245 AS [How Long Going to Provider] FROM #Table1 t1

Note: you need the square-brackets around the field name alias (value from Table2.TextValue) as there are spaces in the string.