tsql - SQL - How to select a column alias from another table? -

i have table in sql data , table holds alias column. used translation purposes.

i wondering how can select on columns retrieve alias table?

this table holds real column names:

id      pageid  colname         order   type    width   isdeleted 1   7   custtype    2   null    null    0 2   7   description 3   null    null    0 3   7   applyvat    4   null    null    0 4   7   produceinvoices 5   null    null    0 5   7   purchasesale    6   null    null    0 6   7   termsdays   7   null    null    0 7   7   datetimelastupdated 8   null    null    0 

this table holds alias (text):

id      colid   userid  text            order   enabled? 50  22  1   id          1       1 51  1   1   custtypes   2   1 52  2   1   description 3   1 53  3   1   applyvat    null    0 54  4   1   produceinvoices null    0 55  5   1   purchasesale    null    0 56  6   1   termsdays   null    0 57  7   1   datetimelastupdated null    0 

i believe need use dynamic sql this, e.g.:

declare @sql nvarchar(max); select top 1 @sql = 'select dt.id ' + at.idalias + ', dt.town ' + at.townalias                    + ' datatable dt' aliastable @ at.languageid = 2; exec(@sql) 

given example of data table

create table datatable (    id int,    town nvarchar(50) ); 

and table holding language - dependent aliases columns in above:

create table aliastable (    languageid int,    idalias nvarchar(100),    townalias nvarchar(100) ); 

sqlfiddle here

one of (many) caveats dynamic sql need ensure alias data validated against sql injectin attacks.