Using PIVOT in SQL Server 2008

by

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;

More Related Contents:

  1. I dont know sql syntax for my table [closed]
  2. Is it possible to write single query on two table which are not connected to each other?
  3. SQL Server dynamic PIVOT query?
  4. CROSS JOIN vs INNER JOIN in SQL
  5. Can I Comma Delimit Multiple Rows Into One Column? [duplicate]
  6. Multiple INSERT statements vs. single INSERT with multiple VALUES
  7. Split function equivalent in T-SQL?
  8. Is the NOLOCK (Sql Server hint) bad practice?
  9. Is having an ‘OR’ in an INNER JOIN condition a bad idea?
  10. Where value in column containing comma delimited values
  11. Parse comma-separated string to make IN List of strings in the Where clause
  12. How to update Identity Column in SQL Server?
  13. SQL query to split column data into rows
  14. Common Table Expression, why semicolon?
  15. T-SQL split string based on delimiter
  16. Bulk DELETE on SQL Server 2008 (Is there anything like Bulk Copy (bcp) for delete data?)
  17. Concatenate values based on ID
  18. Splitting delimited values in a SQL column into multiple rows
  19. Compare DATETIME and DATE ignoring time portion
  20. Split function in SQL Server 2008
  21. SQL “between” not inclusive
  22. Can I set ignore_dup_key on for a primary key?
  23. Stored procedure that exports data into csv files only exports to one file
  24. How do I prevent a database trigger from recursing?
  25. TSQL – How to use GO inside of a BEGIN .. END block?
  26. How to combine results of two queries into a single dataset
  27. When using GETDATE() in many places, is it better to use a variable?
  28. Executing SQL Server Agent Job from a stored procedure and returning job result
  29. SQL take just the numeric values from a varchar
  30. COALESCE Function in TSQL

Leave a Comment