Using PIVOT in SQL Server 2008
Solution 1
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;
Solution 2
Here you 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
Solution 3
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
Mass Dot Net
Updated on September 11, 2020Comments
-
Mass Dot Net over 3 years
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.
-
Mass Dot Net over 14 yearsThis appears to be what I need. I'll give it a shot and report back -- thank you!
-
Rob Farley over 14 yearsPlease bear in mind the subquery. If you just use "SELECT * FROM YourTable", then any other columns involved will affect the implicit grouping that the PIVOT function provides. And if you have any errors, comment out the
exec
line, replacing it withselect @qry
-
Mass Dot Net over 14 yearsI never use [SELECT *] anymore -- I always explicitly state the columns I access -- so that won't be an issue. And yes, I was using [select @qry] for a while so that I could see/debug the generates SQL statement before it ran. Your code worked as promised -- thank you so much for your help!