SQL Server Pivot和老式方法性能对此

set statistics time on

select StudentId,
(select top 1 Answer from AnswerSheet where StudentId=a.StudentId and QuestionId=1) as [What’s your age],
(select top 1 Answer from AnswerSheet where StudentId=a.StudentId and QuestionId=2) as [What’s your gender],
(select top 1 Answer from AnswerSheet where StudentId=a.StudentId and QuestionId=3) as [When do you go home],
(select top 1 Answer from AnswerSheet where StudentId=a.StudentId and QuestionId=4) as [How often do you go to our store]
from AnswerSheet a
group by StudentId

set statistics time off

SQL Server Execution Times:
CPU time = 469 ms, elapsed time = 229 ms.

SQL Server Execution Times:
CPU time = 717 ms, elapsed time = 168 ms.

SQL Server Execution Times:
CPU time = 625 ms, elapsed time = 226 ms.

CPU time大于elapsed time，因为CPU是多核的。

SET statistics TIME ON

SELECT *
FROM (SELECT StudentID
,Col = B.Text
,VALUE = A.Answer
FROM AnswerSheet A
JOIN Question B ON A.QuestionID=B.ID) AS t
pivot (
MAX(t.VALUE) FOR t.Col IN ([What’s your age],[What’s your gender],[WHEN do you GO home],[How often do you GO TO our store])
) AS pt

SET statistics TIME off

SQL Server Execution Times:
CPU TIME = 94 ms, elapsed TIME = 146 ms.

CPU TIME = 62 ms, elapsed TIME = 180 ms.

CPU TIME = 62 ms, elapsed TIME = 122 ms.

说明pivot方法并行度低，但是总花费时间较少。