Logo Questions Linux Laravel Mysql Ubuntu Git Menu
 

Using PIVOT in SQL Server 2008

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.

like image 287
Mass Dot Net Avatar asked Nov 05 '09 00:11

Mass Dot Net


People also ask

What is Pivot Table SQL Server 2008?

The PIVOT operator allows you to rotate data between columns and rows, performing aggregations along the way. UNPIVOT is the inverse of PIVOT, rotating data from columns to rows.

How does PIVOT work in SQL Server?

PIVOT rotates a table-valued expression by turning the unique values from one column in the expression into multiple columns in the output. And PIVOT runs aggregations where they're required on any remaining column values that are wanted in the final output.

Does SQL Server support PIVOT?

pivot and unpivot (SQL Server, Oracle)SQL Server supports the pivot and unpivot clauses since version 2005 (documentation).

When was SQL Server PIVOT added?

Microsoft introduced the PIVOT operator was introduced in SQL Server 2005, and it is this approach that we're going to discuss.


3 Answers

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;
like image 60
Rob Farley Avatar answered Nov 09 '22 06:11

Rob Farley


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
like image 39
Lukasz Lysik Avatar answered Nov 09 '22 04:11

Lukasz Lysik


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
like image 38
aaa Avatar answered Nov 09 '22 05:11

aaa