529

Rename columns in 1 table to row values in another table

Question:

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 varchar value:

Q19243 Q19244 Q19245 </li> <li>

Table 2 has 2 columns ColumnName and TextValue.

</li> </ul>

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

Answer1:

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:

<blockquote>

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.

Recommend

  • VBA Excel 2010 Editor capitalizes property name and it cannot be accessed
  • Encode video from .NET Bitmaps on the go and stream it live over the internet
  • iOS: How to play audio without fps drops?
  • Large items in the notification area (AKA system tray)?
  • How to mount dynamically single file component in Vue.js
  • LatLong falls within a given polygon in D3 + Leaflet
  • Remove every nth element from swift array
  • Aspect advising other aspects
  • Trouble Attaching Call Back to Unobtrusive Validation Show Error
  • Can't get LogCat (Alcatel OneTouch Evolve)
  • Defining variable by logical subseting on time interval in data.table
  • reduce/reduce conflicts using ocamlyacc
  • How to write string.Contains(someText) in expression Tree
  • How to get the index of element in the List in c#
  • Rails AREL .where statement
  • Partial specialization of a class template in derived class affects base class
  • .NET video play library which allows to change the playback rate?
  • How to 'create temp table as select' in Slick?
  • Application level floating views with navigation in Android
  • List images(01.png) and descriptions(01.txt) from directory
  • AndEngine Applying Transparancy to AndEngine View
  • Using a canvas object in a thread to do simple animations - Java
  • MySQL Order by column = x, column asc?
  • Unable to install Git-core+svn by MacPorts
  • Django simple Captcha “No module named fields” error
  • Could not find rake using whenever rails
  • Android fill_parent issue
  • How to use an array of arrays with array_map(…) in PHP?
  • Avoid links criss cross / overlap in d3.js using force layout
  • Initializer list vs. initialization method
  • Nant, Vault & Windows Integrated Authentication
  • Weird JavaScript statement, what does it mean?
  • jquery mobile loadPage not working
  • Properly structure and highlight a GtkPopoverMenu using PyGObject
  • retrieve vertices with no linked edge in arangodb
  • using conditional logic : check if record exists; if it does, update it, if not, create it
  • How can I get HTML syntax highlighting in my editor for CakePHP?
  • Understanding cpu registers
  • Can Visual Studio XAML designer handle font family names with spaces as a resource?
  • Add sale price programmatically to product variations