Using new query as an alias

Question

I have 2 tables:

Table with counters names(t1):

Counter_ID    Counter_Name 
         1          Apples
         2            Nuts
        ...

And table with counters values(t2):

Time   Counter_ID_1  Counter_ID_2
12:00            24            43
13:00            64            73  
     ...

Using SQL I'm trying to name my resulting columns from t2 as they are named in t1 to look like this:

Time         Apples          Nuts
12:00            24            43
13:00            64            73  
     ...

Here is my query (doesn't work):

select
 t2.Time as 'Time',
 t2.Counter_ID_1 as (select Counter_Name from t1 where Counter_ID=1),
 t2.Counter_ID_2 as (select Counter_Name from t1 where Counter_ID=2)
from t2

Any ideas?

UPD: I know, DB design isn't very good, but it's not my DB, I'm just a RO-user :)

UPD2: Thank you all! I understood, that in my case it's easier not to auto it, but to do query one time as I need it and edit aliases manually if table with names (t1) will be changed.

Answer 1

you want dynamic aliases. SQL Server doesnt do that. What you can do, other that redesigning your tables is

select
 Time as 'Time',
 Counter_Name as 'Apples'
 Counter_ID_2 'Nuts'
from t2

but of course, that will only work if you have pre-defined which columns you will be selecting.