从一个表中选择数据，然后根据SQL Server中的另一个表重命名列

I have two tables. TableOne which contains two columns (name & value). TableTwo can contain N no. of columns. Number of rows in TableOne will be equal to number of columns in TableTwo.

我有两张桌子。 TableOne包含两列(名称和值)。 TableTwo可以包含N no。列。 TableOne中的行数将等于TableTwo中的列数。

see the below image for more information.

有关更多信息,请参见下图。

What I want:

我想要的是:

When I run select query on TableTwo, the result-set should pick the column names based on value column of TableOne. We need to match column name of TableTwo with rows available in TableOne and perform transform.

当我在TableTwo上运行select查询时,结果集应该根据TableOne的value列选择列名。我们需要将TableTwo的列名与TableOne中可用的行匹配并执行转换。

So the output should look like this:

所以输出应该如下所示:

ColumnOne | ColumnTwo | columnThree | ColumnFour

 1           1            1             2015-05-08 15:28:22.630
 2           2            2             2015-05-07 15:28:22.630
 ................
 ................

1 个解决方案

#1

You can use dynamic sql to generate the query to execute based on the values in the first table. Here is an example:

您可以使用动态sql根据第一个表中的值生成要执行的查询。这是一个例子:

Declare @dynamicSQL nvarchar(200)

SET @dynamicSQL = 'SELECT ' + (SELECT stuff((select ',' + name + ' AS ' + value from Table1 for xml path('')),1,1,'')) + ' FROM Table2'

SET @dynamicSQL ='SELECT'+(SELECT stuff((选择','+名称+'AS'+来自Table1的值,用于xml路径('')),1,1,''))+'FROM Table2'

EXECUTE sp_executesql @dynamicSQL

SQL Fiddle: http://sqlfiddle.com/#!6/768f9/10

SQL小提琴:http://sqlfiddle.com/#!6/768f9/10