Is it possible to alias a column based on the result of a select+where?

Sorry to say but your table structure is difficult to work with considering what you want to do. There are various ways you can probably get the result, one way would be to use UNPIVOT and PIVOT, but it's ugly.

You could start by UNPIVOTing the data in TB1 from your columns to rows:

select *
from tb1 t1
unpivot 
(
    val
    for col in (A001, A002, A003)
) u

This is going to return the data in the format of:

| COD |  val |  col |
|-----|------|------|
|   1 | cars | A001 |
|   1 | baby | A002 |
|   1 | nasa | A003 |

Then you could take that result and join it to TB2:

select 
    d.COD,
    d.val,
    t2.DESCRIPTION
from
(
    select *
    from tb1 t1
    unpivot 
    (
        val
        for col in (A001, A002, A003)
    ) u
)d
inner join tb2 t2
    on d.col = t2.NO_COL_TB1;

Which gives the result:

| COD |  val | DESCRIPTION |
|-----|------|-------------|
|   1 | cars |   Something |
|   1 | baby |     lasagna |

Now you have your new column names in the Description and the val in rows, but you want them in columns, so now you can apply the PIVOT function to it:

select *
from
(
    select 
        d.COD,
        d.val,
        t2.DESCRIPTION
    from
    (
        select *
        from tb1 t1
        unpivot 
        (
            val
            for col in (A001, A002, A003)
        ) u
    )d
    inner join tb2 t2
        on d.col = t2.NO_COL_TB1
) x
pivot
(
    max(val)
    for description in (Something, Lasagna)
) p;

This generates the final result you want:

| COD | Something | Lasagna |
|-----|-----------|---------|
|   1 |      cars |    baby |

Now all that is great if you know all the columns that you need to UNPIVOT and then PIVOT, but if you don't then you'll need to use dynamic SQL to solve it. Which will look something like this:

DECLARE 
    @colsUnpivot AS NVARCHAR(MAX),
    @colsPivot AS NVARCHAR(MAX),
    @query  AS NVARCHAR(MAX)

select @colsUnpivot 
  = stuff((select ','+quotename(C.column_name)
           from information_schema.columns as C
           where C.table_name = 'tb1' and
                 C.column_name like 'A%'
           for xml path('')), 1, 1, '')

select @colsPivot 
  = stuff((select ','+quotename([DESCRIPTION])
           from Tb2
           for xml path('')), 1, 1, '')


set @query = 'select *
            from
            (
                select 
                    d.COD,
                    d.val,
                    t2.DESCRIPTION
                from
                (
                    select *
                    from tb1 t1
                    unpivot 
                    (
                        val
                        for col in ('+ @colsUnpivot +')
                    ) u
                )d
                inner join tb2 t2
                    on d.col = t2.NO_COL_TB1
            ) x
            pivot
            (
                max(val)
                for description in ('+@colsPivot+')
            ) p;';

exec sp_executesql @query;

It's long, but it should give you the same result. (dbfiddle demo)

DECLARE @SQL NVARCHAR(MAX)

SELECT @SQL = STUFF(
    ( SELECT ', ' + QUOTENAME(C.COLUMN_NAME) + ' AS ' + QUOTENAME(F.DESCRICAO)
FROM INFORMATION_SCHEMA.COLUMNS C 
    INNER JOIN TABBASE F ON C.COLUMN_NAME = F.NO_COL_TABBASE
WHERE C.TABLE_NAME = 'TABELA_ENTRADA'
ORDER BY C.ORDINAL_POSITION FOR XML PATH('')),1,2,'')

SELECT @SQL = 'SELECT ' + @SQL + ' FROM tabela_entrada'

PRINT @SQL

EXECUTE(@SQL)

Is there a better way than this? I didn't know I could use STUFF for this. I found this query on this Microsoft page.