您可以使用动态枢轴，如下所示-

DECLARE @cols AS NVARCHAR(MAX),
@sqlCommand  AS NVARCHAR(MAX);

SELECT  @cols =
STUFF((SELECT   ( '],[' +  A.QUESTION)
    FROM (SELECT DISTINCT QUESTION FROM table2) A
    ORDER BY A.QUESTION 
    FOR XML PATH(''), TYPE
    ).value('.', 'NVARCHAR(MAX)')
,1,1,'')+']'
FROM table2

SET @sqlCommand= 
N'SELECT [ID],NAME,'+SUBSTRING(@cols,2,LEN(@cols))+'
FROM 
(
    SELECT A.ID,A.NAME, B.QUESTION, B.ANSWER 
    FROM table1 A
    INNER JOIN table2 B ON A.ID = B.ID
) AS P
PIVOT
(
    MAX(ANSWER)
    FOR QUESTION IN('+SUBSTRING(@cols,2,LEN(@cols))+')
) PVT'

--PRINT @sqlCommand
EXEC (@sqlCommand)

必须使用pivot来编写此查询：

Sample Data:

DECLARE @Table1 TABLE( id int, name nvarchar(10));
DECLARE @Table2 TABLE( id int, Question nvarchar(10),Answer nvarchar(10))

insert into @Table1 (id,[name])values(1,'AAA')
insert into @Table1 (id,[name])values(2,'BBB')

insert into @Table2 (id,Question,Answer)values(1,'Q1','YES')
insert into @Table2 (id,Question,Answer)values(1,'Q2','NO')
insert into @Table2 (id,Question,Answer)values(1,'Q3','YES')
insert into @Table2 (id,Question,Answer)values(1,'Q4','NO')
insert into @Table2 (id,Question,Answer)values(1,'Q5','YES')
insert into @Table2 (id,Question,Answer)values(2,'Q1','YES')
insert into @Table2 (id,Question,Answer)values(2,'Q2','YES')
insert into @Table2 (id,Question,Answer)values(2,'Q3','YES')
insert into @Table2 (id,Question,Answer)values(2,'Q4','YES')
insert into @Table2 (id,Question,Answer)values(2,'Q5','YES')

select * from @Table1 t1
inner join (
select * from(
select 
t2.id
,t2.Question
,t2.Answer
from @Table2 t2
)temp
pivot
    (
    MAX(Answer)
    for question in ([Q1],[Q2],[Q3],[Q4],[Q5])
    ) as PivotTable 
)TemporaryTable on t1.id=TemporaryTable.id

如果你的问题是动态的，你可以这样写

Declare @cols nvarchar(MAX);
Declare @query nvarchar(MAX);
    set @cols=( select STRING_AGG('['+Question+']',',') from @Table2);
    set @query='
    select * from @Table1 t1
    inner join (
    select * from(
    select 
    t2.id
    ,t2.Question
    ,t2.Answer
    from @Table2 t2
    )temp
    pivot
        (
        MAX(Answer)
        for question in ('+@cols+')
        ) as PivotTable 
    )TemporaryTable on t1.id=TemporaryTable.id
    '
    exec sp_executesql  @query

这不是您通常希望在表示层中执行的操作吗？报告工具就是为此而设计的。