Ok, so I have 2 tables in all:<ul><li>
Table 1 has these 3 columns which are not meaningful as they are just a
Q19243 Q19244 Q19245</li> <li>
Table 2 has 2 columns
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
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 #Table2Answer1:
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
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</blockquote>
Note: you need the square-brackets around the field name alias (value from Table2.TextValue) as there are spaces in the string.