lets say I have a table called tblMain with the column col_a, col_b, col_c
| col_a | col_b | col_c |
|---|---|---|
| Mr A | 01/01/1950 | 12345 |
| Mr B | 02/02/1950 | 99999 |
| Mrs C | 03/03//1950 | 111111 |
I then have a view where I set the names to be more meaningful:
<code>select col_a AS [Customer Name], col_b AS [Customer dob], col_c AS [Customer Order ID] from tblMain
</code>
<code>select col_a AS [Customer Name], col_b AS [Customer dob], col_c AS [Customer Order ID] from tblMain
</code>
select col_a AS [Customer Name], col_b AS [Customer dob], col_c AS [Customer Order ID] from tblMain
| Customer Name | Customer dob | Customer Order |
|---|---|---|
| Mr A | 01/01/1950 | 12345 |
| Mr B | 02/02/1950 | 99999 |
| Mrs C | 03/03//1950 | 111111 |
However, I’d like to hold a mapping table where I can control the name of the outputted fields in the view dynamically.
So a new table called tblMapping with 3 columns table_name, table_field, view_name
| table_name | table_field | view_name |
|---|---|---|
| tblMain | col_a | Customer Name |
| tblMain | col_b | Customer dob |
| tblMain | col_c | Order ID |
As such, the view would ‘look up’ the table name / field in the mapping for each field 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)
<code>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
</code>
<code>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
</code>
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