how to pivot rows to columns

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

  • How do I perform a GROUP BY on an aliased column in MS-SQL Server?
  • creating a stored procedure to return a list of airports lat and long
  • The order of a SQL Select statement without Order By clause
  • Get referenced functions within stored procedure
  • “Operation is not allowed when the object is closed” when executing stored procedure
  • Cannot extract DATE from SSIS 2008 OLEDB Source
  •    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(' ' 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

  • SSRS Column Chart (Bar Chart) needs to appear more clear
  • How to join 3 tables in entity framework 6
  • Execute stored procedure by passing the Script File(.sql) as Parameter
  • How to update specific values in a column with other values from same column in the same table? - SQL Server
  • Eliminate Null Output row
  • SQL count occurence of having count > 2 with condition
  • 3 Solutions collect form web for “how to pivot rows to columns”

    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.

    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).

    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

    MS SQL Server is a Microsoft SQL Database product, include sql server standard, sql server management studio, sql server express and so on.