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

What SQL product is this for? The column-set cannot be variablized in static SQL, only in dynamic sql, which differs a lot between SQL DBMS products. — RBarryYoung, Sep 09 '13 at 16:00
Also, why do you want to do it like this? It's not generally a good approach for SQL except for certain very specific usages. — RBarryYoung, Sep 09 '13 at 16:01
sql server 2008, there must be a way of doing it some how. I guess i need to build a variable containing a comma seperated list of the results from the [selected-columns] column. Then do the select based on the variable. — Stew, Sep 09 '13 at 16:03
I have a system im developing on at work which holds certain data per user depending on the persons level. So a level 1 person will be asked to fill in less fields than a level 4 person. I need a way to select which fields are relevant to the specific level. Its the only way i can think of doing it. — Stew, Sep 09 '13 at 16:06
Yes, as I already mentioned in my first comment, it can be done with dynamic SQL. The question is *why* do you want to do it like this because it's usually not a good idea. And worse, if you do it wrong in an application, you're exposing your DBMS to SQL Injection attacks. So generally we try to suggest a less frangible way of doing these things, but we need to know more about your larger context/task to do that. — RBarryYoung, Sep 09 '13 at 16:09
As i said its the only way i can think of doing it, i need to dymanically know a way of building what goes into the select statement. The page is all on an internal network and is not open to the public. I dont want questions like why am i doing it this way, i need to know how to do it. Im being rushed by my boss to develop this and just need to get something working. The repocussions i do not care about. — Stew, Sep 09 '13 at 16:11
Its all sql server 2008 using sql management studio. I need to be able to extract data based on the dynamic results i need and then can just copy and paste this information into excel for the manager. This query is not going to be available to anybody apart from myself, and it not going to be open to the outside — Stew, Sep 09 '13 at 16:12
Building dynamic sql is simpler with application code such as .net or coldfusion than it is with tsql. Since you said you are in a rush, maybe this is a better approach. — Dan Bracuk, Sep 09 '13 at 16:15
Are you able to define your data tables, or is it to late for that? Organizing your data as name value pairs would be a potential solution for you here. Sorry, from reading update comments, this doesn't look all too possible now. — Twelfth, Sep 09 '13 at 16:18
From comments - it might be easier to limit the fields that can be seen in whatever your clients are accessing the data from and not on the SQL level. — Twelfth, Sep 09 '13 at 16:20
Dont want to develop a front end in any other language. Im basically hoping to just end up with a stored procedure that i can run myself where i just enter the level number, which will be from 1 to 4. It will look to see what columns are required for that level and then will select only them columns. The group_concat is something that would of been perfect, if it was available in sql server — Stew, Sep 09 '13 at 16:39

Praveen Lobo · Answer 1 · 2013-09-09T17:17:38.917

9

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;

edited Sep 09 '13 at 17:17

answered Sep 09 '13 at 16:18

Praveen Lobo

6,956
2
28
40

Thank you, very helpful but im getting the following error 'GROUP_CONCAT' is not a recognized built-in function name. – Stew Sep 09 '13 at 16:25
@user1498304 - Because you are on SQL Server 2008 and `GROUP_CONCAT` is MySQL only. – Martin Smith Sep 09 '13 at 16:26
ah! now I see it. It's tagged as sql-server. lost 2 points for nothing. – Praveen Lobo Sep 09 '13 at 16:27
You'll get them back if/when you delete the answer. – Martin Smith Sep 09 '13 at 16:28
Im sorry this doesnt work either, i get the following errors Msg 102, Level 15, State 1, Line 2 Incorrect syntax near '('. Msg 156, Level 15, State 1, Line 2 Incorrect syntax near the keyword 'for'. Msg 102, Level 15, State 1, Line 3 Incorrect syntax near '('. Msg 137, Level 15, State 2, Line 4 Must declare the scalar variable "@query". This is using the same sql above, apart from changing the table and column names. – Stew Sep 10 '13 at 08:21

David Weinberg · Answer 2 · 2013-09-11T08:58:23.103

0

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

edited Sep 11 '13 at 08:58

answered Sep 09 '13 at 17:17

David Weinberg

1,033
1
13
29

Tried this one too but following errors Msg 139, Level 15, State 1, Line 0 Cannot assign a default value to a local variable. Msg 137, Level 15, State 2, Line 2 Must declare the scalar variable "@cmd". Msg 137, Level 15, State 2, Line 5 Must declare the scalar variable "@cmd". Msg 137, Level 15, State 2, Line 7 Must declare the scalar variable "@cmd". Msg 137, Level 15, State 2, Line 8 Must declare the scalar variable "@cmd". – Stew Sep 10 '13 at 08:25
You ruight. it's because you use pld version of SQL SERVER. 2005 or less. You can check your SQL SERVER version "select @@VERSION" – David Weinberg Sep 11 '13 at 09:13

score 0 · Accepted Answer · answered Sep 10 '13 at 09:03

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)

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

3 Answers3

Linked