以下是动态执行操作的方法:
create table #t1(QID int, Question char(10))
insert #t1 values
(1, 'Question 1'),
(2, 'Question 2'),
(3, 'Question 3'),
(4, 'Question 4'),
(5, 'Question 5')
create table #t2 (QID int, Answer char(3), Parent int)
insert #t2 values
(1, 'Yes', 123),
(2, 'No ', 123),
(3, 'No ', 123),
(4, 'Yes', 123),
(5, 'Yes', 123),
(1, 'No ', 124),
(2, 'Yes', 124),
(3, 'No ', 124),
(4, 'No ', 124),
(5, 'No ', 124)
declare @collist nvarchar(max)
SET @collist = stuff((select distinct ',' + QUOTENAME(Question)
FROM #t1 -- your table here
FOR XML PATH(''), TYPE
).value('.', 'NVARCHAR(MAX)')
,1,1,'')
select @collist
declare @q nvarchar(max)
set @q = '
select *
from (
select
Question, Answer, Parent
from (
select #t1.*, #t2.Answer, #t2.parent
from #t1
inner join #t2 on #t1.QID = #t2.QID
) as x
) as source
pivot (
max(Answer)
for Question in (' + @collist + ')
) as pvt
'
exec (@q)