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

I have a table like this:

TB1:

COD | A001 | A002 | A003
 1    cars   baby   nasa

and then a second table:

TB2:

COD | NO_COL_TB1 | DESCRIPTION |
 1  |    A001    |  Something
 2  |    A002    |   lasagna

What I'm trying to do is something like this ( obviously this doesnt work )

select A001 as (select description from TB2 WHERE no_col_tb1= A001 )
      ,A002 AS (select description from TB2 WHERE no_col_tb1= A002 )
from TB1

i tried with inner joins and some dynamic sql but I just can't think in a logic for this. There's some questions about this but none of them could help me. I think this is impossible to do with a single statement and only possible with a Dynamic SQL.

EDIT:

The correct result would be:

SELECT COD,
       TB1.A001 AS 'Something',
       TB1.A002 AS 'Lasagna'
FROM TB1

and the result would be:

COD |SOMETHING | LASAGNA |
 1      cars       baby

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)

StackExchange Database Administrators Q#241656, answer score: 25