将 1 个表中的列重命名为另一个表中的行值 [英] Rename columns in 1 table to row values in another table

问题描述

好的，所以我总共有 2 张桌子:

Ok, so I have 2 tables in all:

• Table 1 有这 3 列没有意义，因为它们只是一个 varchar 值:

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

Q19243  Q19244  Q19245

Table 2 有 2 列 ColumnName 和 TextValue.

Table 2 has 2 columns ColumnName and TextValue.

ColumnName 保存了Table 1(Q19243 等)中 3 列的名称的值，并且还有一个名为 TextValue 包含对 Q19243 实际含义的友好描述.

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.

所以Table 2中有3条记录，Table 1中的每一列对应1条记录.

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

我想将 Table 1 中的这 3 列重命名为等于 Table 2 中 TextValue 列中的任何内容.我想动态地执行此操作，而不是使用简单的 UPDATE 语句来重命名列.抱歉，我没有附加屏幕截图，但我没有看到附加按钮...

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...

如果您运行此代码来创建 2 个表的示例，那么您可能应该对我所指的内容有更好的了解.

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

推荐答案

由于列重命名的目的仅用于输出目的，因此您可以使用针对 Table2 的查询来创建特定于 Table1 的动态 SQL，该动态 SQL 将列名称设置为选择.

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

SELECT @SQL= 查询后的 @SQL 的值为:

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

SELECT t1.Q19243 AS [提供商名称]，t1.Q19244 AS [提供商您通常见], t1.Q19245 AS [多长时间到供应商] FROM #Table1t1

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

注意:您需要在字段名称别名(来自 Table2.TextValue 的值)周围使用方括号，因为字符串中有空格.

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

这篇关于将 1 个表中的列重命名为另一个表中的行值的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！