在 SQL Server 2008 中使用 PIVOT

假设我有一些数据，无论是在 SQL Server 2008 表中还是在 [表] 类型的变量中:

author_id review_id question_id answer_id88540 99001 1 71988540 99001 2 72088540 99001 3 72188540 99001 4 72288540 99001 5 72336414 24336 1 30236414 24336 2 30336414 24336 3 30436414 24336 4 30536414 24336 5 306

我想检索数据作为如下所示的结果集:

author_id review_id 1 2 3 4 588540 99001 719 720 721 722 72336414 24336 302 303 304 305 306

我怀疑 PIVOT 运算符是我需要的(根据 thispost，无论如何)，但我不知道如何开始，尤其是当表中 question_id 行的数量可能会有所不同时.在上面的示例中，它是 5，但在另一个查询中，该表可能填充有 7 个不同的问题.

解决方案

实际上，您最好在客户端执行此操作.假设您正在使用 Reporting Services，根据您的第一个结果集获取数据并使用矩阵显示它，在行组中使用 author_id 和 review_id，在列组中使用 question_id，在中间使用 MAX(answer_id).

查询是可行的，但您现在需要动态 SQL.

...类似:

DECLARE @QuestionList nvarchar(max);SELECT @QuestionList = STUFF((SELECT ', ' + 引用名(question_id)从你的表GROUP BY question_id按 question_id 排序FOR XML 路径('')), 1, 2, '');声明@qry nvarchar(max);SET @qry = 'SELECT author_id, review_id, ' + @QuestionList +FROM (SELECT author_id, review_id, question_id, answer_id从你的表)枢(MAX(AnswerID) FOR question_id IN (' + @QuestionList + ')) pvtORDER BY author_id, review_id;';exec sp_executesql @qry;

Let's say I have some data, either in a SQL Server 2008 table or a [table]-typed variable:

author_id     review_id     question_id     answer_id
88540         99001         1               719
88540         99001         2               720
88540         99001         3               721
88540         99001         4               722
88540         99001         5               723
36414         24336         1               302
36414         24336         2               303
36414         24336         3               304
36414         24336         4               305
36414         24336         5               306

I want to retrieve the data as a result set that looks like this:

author_id     review_id     1     2     3     4     5
88540         99001         719   720   721   722   723
36414         24336         302   303   304   305   306

I suspect the PIVOT operator is what I need (according to this post, anyway), but I can't figure out how to get started, especially when the number of question_id rows in the table can vary. In the above example, it's 5, but in another query the table might be populated with 7 distinct questions.

解决方案

Actually, you'd be better off doing this in the client. Suppose you're using Reporting Services, get the data as per your first result set and display it using a Matrix, with author_id and review_id in the Row Group, question_id in the Column Group, and MAX(answer_id) in the middle.

A query is doable, but you'd need dynamic SQL right now.

...something like:

DECLARE @QuestionList nvarchar(max);
SELECT @QuestionList = STUFF(
(SELECT ', ' + quotename(question_id)
FROM YourTable
GROUP BY question_id
ORDER BY question_id
FOR XML PATH(''))
, 1, 2, '');

DECLARE @qry nvarchar(max);
SET @qry = '
SELECT author_id, review_id, ' + @QuestionList + 
FROM (SELECT author_id, review_id, question_id, answer_id
      FROM YourTable
     ) 
PIVOT
(MAX(AnswerID) FOR question_id IN (' + @QuestionList + ')) pvt
ORDER BY author_id, review_id;';

exec sp_executesql @qry;