Select Specific Columns Based on Specific Row in Another Table

Question

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

Table A:

PK	ManagerKey	Table	ColumnOne	ColumnTwo
51	101	DataBase1.dbo.Table1	TestingColumn1	TestingColumn5
29	201	DataBase1.dbo.Table6	StagingColumn3	StagingColumn4
37	301	DataBase1.dbo.Table3	ProductionColumn4	ProductionColumn9

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

PK	TestingColumn1	TestingColumn2	TestingColumn3	TestingColumn4	TestingColumn5
565	$156	$870	StringOne	StringTwo	StringThree
876	$250	$840	StringOne	StringTwo	StringThree
151	$165	$950	StringOne	StringTwo	StringThree

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:

PK	TestingColumn1	TestingColumn5
565	$156	StringThree
876	$250	StringThree
151	$165	StringThree

Any help is appreciated. Thank you.

Using a relational database to generically store data like this completely defeats the purpose of having a relational database. And your performance will be terrible — Dale K
– Dale K, Commented Jun 4, 2022 at 23:11

Stu · Accepted Answer · 2022-06-04 22:49:27Z

1

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.

edited Jun 4, 2022 at 22:49

answered Jun 4, 2022 at 22:44

Stu

32.7k6 gold badges17 silver badges34 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

AlpacaRG Over a year ago

Thank you very much for your time and help. It works flawlessly. I appreciate it

Collectives™ on Stack Overflow

Select Specific Columns Based on Specific Row in Another Table

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related