How do I use the results of one query as the selected columns for another query?

Question

I have a table (DataImportTable) that contains column names of the MainDataTable as its records - please see image: Target Column Table

I would like to use the above-mentioned column names in a query to look only them up from my MainDataTable.

The query that obviously doesn't work, however can explain what I am trying to achieve, can be seen below:

select (select TargetColumn from DataImportTable) from MainDataTable

I hope this makes sense and that someone can be of assistance.

Thank you in advance!

Answer 1

Use Dynamic SQL to get just the columns that are present in other table

Select row_number() over( order by TargetColumn ) as RowNumber,TargetColumn into
#temp from DataImportTable where TargetColumn is not null

Declare @Columnslist varchar(max),@i int
set @i = 1
while @i<= (select Count(*) from #temp)
begin
(SELECT @Columnslist = isnull(@Columnslist,'')+'['+(select TargetColumn from #temp 
where RowNumber = @i)+'],')
set @i = @i + 1;
end

SELECT @Columnslist = LEFT(@Columnslist, LEN(@Columnslist) - 1)

Declare @select_cmd varchar(max)
set @select_cmd='select '+@Columnslist +' From MainDataTable'

EXEC(@select_cmd);

this answer makes it even simpler

exec('select '+(select Stuff((select ','+TargetColumn from DataImportTable
for xml path('')),1,1,''))+' From MainDataTable')

Answer 2

You could do something like this:

declare @query varchar(max);
declare @columns varchar(max);

set @columns = (select TargetColumn + ', ' from DataImportTable for xml path(''));
set @query = 'select ' + left(@columns, len(@columns) -1) + ' from MainDataTable';

exec(@query);

First @columns is constructed as the values of the TargetColumn, comma-separated (with a trailing comma). Then, the columns are used to construct a query (the left-thing removes the trailing comma), which is in turn executed.

Answer 3

try select (select TargetColumn from DataImportTable) as TargetColumn from MainDataTable What's wrong with this MySQL query? SELECT * AS `x`, how to use x again later?