SQL - How to select a column alias from another ta

问题:

I have a table in SQL with data and another table that holds the alias for that column. It is used for translation purposes.

I was wondering how can I do a select on those columns but retrieve the alias from another table?

This is the table that holds the 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 is the table that holds the 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

回答1:

I believe you will need to use dynamic sql to do this, e.g.:

DECLARE @Sql NVARCHAR(MAX);
SELECT TOP 1 @Sql = 'SELECT dt.ID as ' + at.IDAlias + ', dt.Town as ' + at.TownAlias 
                  + ' FROM DataTable dt'
FROM AliasTable at
WHERE at.LanguageID = 2;
EXEC(@Sql)

Given the example of Data Table

CREATE TABLE DataTable
(
   ID INT,
   Town NVARCHAR(50)
);

And a table holding language - dependent aliases for the columns in the above:

CREATE TABLE AliasTable
(
   LanguageId INT,
   IDAlias NVARCHAR(100),
   TownAlias NVARCHAR(100)
);

SqlFiddle here

One of the (many) caveats with dynamic Sql is you will need to ensure that the alias data is validated against Sql Injectin attacks.