I have a table called tblMain with the columns col_a, col_b, col_c

I then have a view where I set the names to be more meaningful:

select
    col_a AS [Customer Name]
    , col_b AS [Customer dob]
    , col_c AS [Customer Order ID]
from tblMain

However, I’d like to hold a mapping table where I can control the name of the outputted columns in the view dynamically.

So a new table called tblMapping with 3 columns table_name, table_field, view_name

As such, the view would ‘look up’ the table name / column in the mapping for each column in the view and name it as the view name. I know how to do it in python and it’s relatively straight forward, but I’m not as proficient with SQL.

So something like (which I know is not right btw)

select
    col_a AS (select view_name from tblMapping where table_name = tbl1 and field_name = table_field)
    , col_b AS etc
    , col_c AS etc
from tblMain as tbl1

Could anyone point me in the right direction please?

Thanks