根据另一个表中的列名从一个表中选择列

I have 2 tables, one that contains the final results I need, and another that contains the list of columns I need to select from based on a set level.

我有2个表,一个包含我需要的最终结果,另一个包含我需要根据设置级别选择的列列表。

For example :

例如 :

table_levels

level | name      | [selected-columns]
1     | startdate | start_date
1     | end date  | end_date
1     | contract  | contract

So if i do the following

所以,如果我做以下

select column from table_levels where level = '1'

Then basically i need to use the column names from this select statement to determine which columns are selected from another statement.

然后基本上我需要使用此select语句中的列名来确定从另一个语句中选择哪些列。

This is what ive tried and of course i know its wrong but gives you an idea of what im trying to do.

这是我尝试过的,当然我知道它的错误,但让你知道我想做什么。

select (select [selected-columns] from table_levels where nwlevel = '1')
from table_results

In a way im trying to dynamically build an sql query which can be altered by whichever columns i put in the table_levels table.

在某种程度上,我试图动态构建一个SQL查询,可以通过我放在table_levels表中的任何列来更改。

This should in theory act the same as the following sql query

理论上,这应该与以下sql查询相同

select start_date, end_date, contract
from table_results

3 个解决方案

#1

My previous answer was for mysql. Since the tag has been updated on the question since then, here is the query for sql-server-2008.

我之前的回答是mysql。由于此后标签已在问题上更新,因此这里是sql-server-2008的查询。

Build a list of columns from the values in table_levels, remove the last ,, build a query string to get you the results from table_results, and then execute.

从table_levels中的值构建列列表,删除最后一个,构建一个查询字符串以从table_results获取结果,然后执行。

DECLARE @listStr varchar(MAX) = ( select selectColumnName + ',' from table_levels where level = 1 for xml path(''))
DECLARE @query varchar(MAX) = 'SELECT ' + LEFT(@listStr, LEN(@listStr)-1) + ' FROM table_results'
execute(@query)

Demo for sql server

为sql server演示

Previous answer. Works for mssql

以前的答案。适用于mssql

See demo for mysql

请参阅mysql演示

Use GROUP_CONCAT to make a string out of the values in table_levels and then build a query string to get you the results from table_results

使用GROUP_CONCAT从table_levels中的值中创建一个字符串,然后构建一个查询字符串以获取table_results的结果

SET @listStr = ( SELECT GROUP_CONCAT(selectColumnName) FROM table_levels where level = 1);
SET @query := CONCAT('SELECT ', @listStr, ' FROM table_results');
PREPARE STMT FROM @query;
EXECUTE STMT;

#2

I got it to work by doing what @lobo said with a slight change.

我通过做一些@lobo稍微改变的说法来实现它。

DECLARE @listStr varchar(MAX);
set @liststr =
        (
select [column] + ',' from dbo.columns where nwlevel = '1' for xml path('')
        )

DECLARE @query varchar(MAX);
set @query =
        (
        'SELECT ' + LEFT(@listStr, LEN(@listStr)-1) + ' FROM staff'
        )
execute(@query)

#3

declare @cmd varchar(max)
select @cmd=''
select @cmd=@cmd+','+[selected-columns]
from table_levels
where level=1
if len(@cmd)>0
begin
    select @cmd='select '+substring(@cmd,2,len(@cmd))+' from table_result'
    exec(@cmd)
end

#1