在SQL Server 2008中使用PIVOT [英] Using PIVOT in SQL Server 2008

问题描述

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

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

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

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

我怀疑我需要PIVOT运算符(根据 this帖子)，但我不知道如何开始使用，尤其是当表中的 question_id 行数可以变化时.在上面的示例中，值为5，但在另一个查询中，表中可能填充了7个不同的问题.

解决方案

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

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

...类似:

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;

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;

这篇关于在SQL Server 2008中使用PIVOT的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！