How to replace column names with a value of another table

Question

I have table A like below:

ID  QuestionCode   Question
1    Q1            What is your name
2    Q2            How old are you
3    Q3            Where are you from

Also, I have table B like below:

UserId  Q1     Q2   Q3
1       Ahmad  22   Egypt
2       John   31   USA
3       Daniel 32   Mexico

What I want is to create a view using the question itself instead of the its code in the column name like:

UserId    What Is Your Name    How old are you    Where are you from
1         Ahmad                22                 Egypt
2         John                 31                 USA
3         Daniel               32                 Mexico

What is the best way to do it? Any help would be appreciated.

THanks.

Answer 1

Updated ... Missed the 2016 tag

SQL Server is declarative by design and does not support macro substitution. This leaves Dynamic SQL.

Here is a working example of the Dynamic SQL

Declare @tsql nvarchar(max) = N'Select top 1 * from Answers 

Select @tsql = 'Select '+stuff((Select ',' +expr From (
                                                      select expr=concat(coalesce(quotename(Question),Name),'=',Name)
                                                       From  sys.dm_exec_describe_first_result_set(@tsql,null,null )   A
                                                       Left Join Questions B on B.QuestionCode=A.name
                                                      ) src For XML Path ('')),1,1,'')+' From Answers'
 
Exec(@tsql)

Results