how to pivot rows to columns

Question

Every example of using PIVOT in MSSQL shows people using this to aggregate data. I'm trying to exploit this to just simply transpose rows to columns

For instance, consider the follwoing data

   SELECT 11826 ID,cast('64 ' as varchar(1000)) as answer,75098 QuestionID,2785 CollectionID  into #temp
insert into #temp SELECT 11827 ID,cast('Security ' as varchar(1000)) as answer,75110 QuestionID,2785 CollectionID 
insert into #temp SELECT 11828 ID,cast('42 ' as varchar(1000)) as answer,75115 QuestionID,2785 CollectionID 
insert into #temp SELECT 11829 ID,cast('3/23/2010 12:01:00 AM ' as varchar(1000)) as answer,75119 QuestionID,2785 CollectionID 
insert into #temp SELECT 11830 ID,cast('3/25/2010 ' as varchar(1000)) as answer,75120 QuestionID,2785 CollectionID 
insert into #temp SELECT 11898 ID,cast('67 ' as varchar(1000)) as answer,75313 QuestionID,2792 CollectionID 
insert into #temp SELECT 11899 ID,cast('True ' as varchar(1000)) as answer,75314 QuestionID,2792 CollectionID 
insert into #temp SELECT 11900 ID,cast('0 ' as varchar(1000)) as answer,75315 QuestionID,2792 CollectionID 
insert into #temp SELECT 11901 ID,cast('[email protected] ' as varchar(1000)) as answer,75316 QuestionID,2792 CollectionID

The results should yield something like

CollectionID   [AnswerFor75098]     [AnswerFor75110]     [AnswerFor75115]     [AnswerFor75315]...
2785             64                  Security            42                   
2792             Null               Null                 Null                  67

I've been experimenting with PIVOT however i'm not sure this is the correct solution. If so, does anybody have a hint i could use? I think i can probably do this in a stored procedure fairly however, i'm trying to avoid using cursors if possible.

Thanks for the help

Answer 1

One thing that people often don't realize when they try PIVOT is that you need to know your columns and hard-code them into the query. This leads us to think, "is this really the best way?"

You can't write a general-purpose SQL query that adds columns dynamically based on data values, even when you use Microsoft's PIVOT syntax.

PIVOT is still a bit more convenient than writing a bunch of JOINs, so I'd suggest going with PIVOT (if you use Microsoft SQL Server exclusively).

Answer 2

What you are seeking is a crosstab query. If you know the question numbers you seek ahead of time, you can do it like so:

Select CollectionId
    , Min( Case When Question = 75098 Then Answer End ) As AnswerFor75098
    , Min( Case When Question = 75110 Then Answer End ) As AnswerFor75110
    , Min( Case When Question = 75115 Then Answer End ) As AnswerFor75115
    , Min( Case When Question = 75315 Then Answer End ) As AnswerFor75315
From #Temp
Group By CollectionId

However, if what you want is to dynamically determine the columns based on questions, then what you want is a dynamic crosstab and that cannot be done in SQL Server without some fugly dynamic SQL. Instead, you should do this in the middle-tier or in a reporting tool.

Answer 3

I have done a quick Sql to show how to use Pivot with your sample data. I think it can be improved to acheive what you want.

Select CollectionId,[75098],[75110],[75113],[75114],[75115] from
(Select QuestionID ,CollectionId, answer From #temp group by QuestionID,CollectionId, answer) as ST
    PIVOT (count(QuestionID) for QuestionID in ([75098],[75110],[75113],[75114],[75115]))as PT
 group by CollectionId,[75098],[75110],[75113],[75114],[75115] 

@community, please feel free to suggest any amendments to this suggestion