I have two tables. One table contains information on where to find information in another table.

Table A:

DataBase1.dbo.Table1 (Each of the three tables have the same structure, but I will only be using DataBase1.dbo.Table1 in this example):

If I narrow down the results of Table A to a single row, how can I use the row results in ColumnOne and ColumnTwo of Table A to return the desired columns of the second table? The database listed in the Table column of Table A will always be the same (DataBase1). However, the table names are different, as shown in the example.

Expected results:

Any help is appreciated. Thank you.

CodePudding user response：

You would need to dynamically build the SQL to execute - I would have honestly thought how to do this would have been established before implementing this design!

There's no real need to have this clunky requirement for dynamic SQL, this could all be contained in a single configuration table.

Something like:

declare @sql nvarchar(max);

select @sql = Concat('select ', QuoteName(columnone), ', ', QuoteName(columntwo), ' from ', [table])
from A
where pk=51;

exec (@sql);

I would suggest that naming a column "Table" be avoided since it's a reserved word and the contents is not a table but a fully qualifed object name.