Rename columns dynamically

Transact-SQL
1

I have hundreds of tables with what I'll call "stupid column names" that make absolutely no sense whatsoever. Because this table is populated from several outside vendors, it isn't practical to have each vendor change their code to use sensible column names. This brings me to the problem. Our client requires certain column names. It isn't feasible to hard code every query similar to the following:

SELECT Col1 As Name, Col2 As Address FROM Table1;

Initially we thought it might be a simple proposition (it may be we just don't know how to do it) to build a table with three columns (ID, OldHeader, NewHeader) then populate this table as a sort of lookup table to match the column on so we could get an output that we desire. So what we could do is build a query that looks at the lookup table and says "Oh, the old header is Col1, but it should be Name, so lets SELECT Col1 As Name from Table1"

I don't even know how to start ... maybe nested SELECTs? Maybe dynamic SQL?

I know this doesn't work, but if it was C++ this is sort of how I would handle it.
Consider the following scenario:
MasterTable is one of the tables that holds data we need to obtain, but with bad column header names.
LookupTable is a table with bad names and good names

I want to be able to do this
SELECT OldName FROM LookupTable AS NewName FROM LookupTable FROM MasterTable;

I'd greatly appreciate any insight, nudge, or violent shove in the right direction.

2

You could create a view and select from that.

3

Yeah, I would just create VIEWs as well.

Not the same question, but we have tables with names like XXX_YYY_ZZZ_TableName and we have a view XXX_YYY_ZZZ_View which SELECTs all the columns from the table, and also all the Code Lookup descriptions, so in general we always make our queries from the VIEW rather than the TABLE - saves having to make all the JOINs in every query, and in the event that anything changes it is centralised