在 SQL Server 2008 中使用 PIVOT
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/1677645/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Using PIVOT in SQL Server 2008
提问by Mass Dot Net
Let's say I have some data, either in a SQL Server 2008 table or a [table]-typed variable:
假设我有一些数据，无论是在 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
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_idrows 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.
我怀疑 PIVOT 运算符是我所需要的（无论如何，根据这篇文章），但我不知道如何开始，尤其是当表中的question_id行数可能有所不同时。在上面的示例中，它是 5，但在另一个查询中，该表可能填充了 7 个不同的问题。
回答by Rob Farley
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.
实际上，您最好在客户端执行此操作。假设您正在使用 Reporting Services，根据您的第一个结果集获取数据并使用矩阵显示它，在行组中使用 author_id 和 review_id，在列组中使用 question_id，在中间使用 MAX(answer_id)。
A query is doable, but you'd need dynamic SQL right now.
查询是可行的，但您现在需要动态 SQL。
...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;
回答by Lukasz Lysik
Hereyou have great example and explanation.
在这里你有很好的例子和解释。
In your case it would be like this:
在你的情况下，它会是这样的：
SELECT author_id, review_id, [1], [2], [3], [4], [5]
FROM
(
SELECT author_id, review_id, question_id, answer_id
FROM the_table
) up
PIVOT (MAX(answer_id) FOR question_id IN ([1],[2],[3],[4],[5])) AS pvt
回答by aaa
SELECT author_id, review_id, [1], [2], [3], [4], [5]
FROM
(
SELECT author_id, review_id, question_id, answer_id
FROM the_table
) up
PIVOT (MAX(answer_id) FOR
回答by Cade Roux
See this answer
看到这个答案
Basically, you pre-inspect the data to get the columns and then dynamically generate the SQL using the dynamic pivot list. There's really no non-dynamic way, because the definition of the columns in the set you want to return is not fixed.
基本上，您预先检查数据以获取列，然后使用动态数据透视表动态生成 SQL。确实没有非动态方式，因为您要返回的集合中列的定义不是固定的。
回答by Nestor
select *
from @t pivot
(
max(answer_id) for question_id in ([1],[2],[3],[4],[5])
) pivotT
The only way to vary the list ([1],[2],[3],[4],[5]) would be to build this query in a string (dynamically) and then execute it.
改变列表 ([1],[2],[3],[4],[5]) 的唯一方法是在字符串中（动态地）构建这个查询，然后执行它。
