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

Question

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

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

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;