在SQL Server 2008中使用PIVOT

查询是可行的，但是您现在需要动态SQL. ...类似:DECLARE @QuestionList nvarchar(max);SELECT @QuestionList = STUFF((SELECT ', ' + quotename(question_id)FROM YourTableGROUP BY question_idORDER BY question_idFOR 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 + ')) 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_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 306I want to retrieve the data as a result set that looks like this:author_id review_id 1 2 3 4 588540 99001 719 720 721 722 72336414 24336 302 303 304 305 306I 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 YourTableGROUP BY question_idORDER BY question_idFOR 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 + ')) pvtORDER BY author_id, review_id;';exec sp_executesql @qry; 这篇关于在SQL Server 2008中使用PIVOT的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！ 上岸，阿里云！