CODEWITHSUNDEEP
sqlsql-server-2008
Stew
Stew

Reputation: 305

Select columns from one table based on the column names from another table

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.

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.

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.

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

select start_date, end_date, contract
from table_results

Upvotes: 6

Views: 23270

Answers (3)

David Weinberg
David Weinberg

Reputation: 1091

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

Upvotes: 0

Stew
Stew

Reputation: 305

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

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)

Upvotes: 0

Praveen Lobo
Praveen Lobo

Reputation: 7187

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.

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. 

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

Previous answer. Works for mssql

See demo for 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

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;

Upvotes: 9

Related Questions