Using table entries for query column headers of another table

Transact-SQL
1

I have a table called lkp_Product_makeup with columns fld_name, Friendly_Name

Example

fld_name,Friendly_Name

Text_1,Product Name
Text_2,Description

I have another table called tbl_Products that has the following columns

Text_1,Text_2

I want to create a query for tbl_Products that would get the column headers from lkp_Product_makeup.Friendly_Name using it's actual header as the search in lkp_Product_makeup.fld_name if that makes sense.

Is this possible?

2

Try:

IF Object_Id(N'dbo.lkp_Product_makeup') IS NOT NULL 
   DROP TABLE dbo.lkp_Product_makeup;
GO
CREATE TABLE dbo.lkp_Product_makeup
   ( fld_name varchar(50)
   , Friendly_Name varchar(50)
   );

INSERT dbo.lkp_Product_makeup
      ( fld_name, Friendly_Name )
VALUES
     ( 'Text_1', 'Product Name'  )     
   , ( 'Text_2', 'Description'  );

IF Object_Id(N'dbo.tbl_Products') IS NOT NULL 
   DROP TABLE dbo.tbl_Products;
GO
CREATE TABLE dbo.tbl_Products
   ( Text_1 varchar(50)
   , Text_2 varchar(50)
   );

INSERT dbo.tbl_Products
      ( Text_1, Text_2 )
VALUES
   ( 'Green widget', 'Really pretty green widget' )
 , ( 'Orange widget', 'Ugly orange widget' );
GO

DECLARE @sql varchar(8000)='SELECT '

DECLARE c CURSOR FOR 
SELECT 
     CASE WHEN column_id=1 THEN ' ' ELSE ' ,' END + l.fld_name
   , QuoteName(Friendly_Name)
FROM 
   dbo.lkp_Product_makeup l
JOIN 
   sys.columns c 
   ON c.name=l.fld_name
WHERE 
   c.object_id=Object_Id(N'dbo.tbl_Products');

DECLARE 
     @Fld varchar(50)
   , @FrName varchar(50);

OPEN c;

FETCH c INTO @Fld, @FrName;

WHILE @@FETCH_STATUS=0
BEGIN

   SET @sql+=@Fld+' '+@FrName; 

   FETCH c INTO @Fld, @FrName;

END

CLOSE c;

DEALLOCATE c;

SET @sql+=' FROM dbo.tbl_Products';

EXEC(@sql);

DROP TABLE dbo.lkp_Product_makeup;

DROP TABLE dbo.tbl_Products;
GO