8

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.

Community
  • 1
  • 1
Mass Dot Net
  • 2,150
  • 9
  • 38
  • 50

5 Answers5

9

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;
Rob Farley
  • 15,625
  • 5
  • 44
  • 58
  • This appears to be what I need. I'll give it a shot and report back -- thank you! – Mass Dot Net Nov 05 '09 at 01:19
  • 1
    Please 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 with `select @qry` – Rob Farley Nov 05 '09 at 01:33
  • I 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! – Mass Dot Net Nov 05 '09 at 14:08
3

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
Lukasz Lysik
  • 10,462
  • 3
  • 51
  • 72
2
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
aaa
  • 21
  • 1
0
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.

Nestor
  • 13,706
  • 11
  • 78
  • 119
0

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.

Community
  • 1
  • 1
Cade Roux
  • 88,164
  • 40
  • 182
  • 265