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

  • Can't identify reason for BULK INSERT errors
  • SQL poor stored procedure execution plan performance - parameter sniffing
  • Get latest row and sum of a column in it togather
  • BULK INSERT with identity (auto-increment) column
  • Known issue?: SQL Server 2005 stored procedure fails to complete with a parameter
  • What does a Status of “Suspended” and high DiskIO means from sp_who2?
  •    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

  • TSQL Passing a varchar variable of column name to SUM function
  • Why is CREATE TABLE command not working in a user-defined transaction?
  • querying binary column using like in sql server
  • How can I set bit column to null?
  • Sql Data Type for Primary Key - SQL Server?
  • create stored procedure if doesn't exist in sql server
  • 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.