SQL Server 2017

By using db<>fiddle, you agree to license everything you submit by Creative Commons CC0.
CREATE TABLE Tb1
    ([COD] int, [A001] varchar(4), [A002] varchar(4), [A003] varchar(4))
;
    
INSERT INTO Tb1
    ([COD], [A001], [A002], [A003])
VALUES
    (1, 'cars', 'baby', 'nasa')
;


CREATE TABLE Tb2
    ([COD] int, [NO_COL_TB1] varchar(4), [DESCRIPTION] varchar(9))
;
    
INSERT INTO Tb2
    ([COD], [NO_COL_TB1], [DESCRIPTION])
VALUES
    (1, 'A001', 'Something'),
    (2, 'A002', 'lasagna')
;
3 rows affected

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;


COD
Something
Lasagna


1
cars
baby


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;


COD
Something
lasagna


1
cars
baby