Results 1 to 2 of 2
  1. #1
    Join Date
    Apr 2006
    Posts
    1

    Question Unanswered: Need Help with either using Pivot or Crosstabs

    Hi everybody,

    I am a bit of a SQL Server 2005 newbie. I have to run a report extracting survey responses for my group using SQL Server. My question is what is the best way to organize the data so it converts from rows into columns. I know SQL Server was not meant for creating presentation reports but I have over 800 surveys that I compile so any help would be appreciate. I have to extract data from 2 tables within a view.

    For example, I would like the data show in this format:

    Last Name|First Name|Question1|Question2|Question3|..etc
    Doe|John|answer1|answer2|answer3|...etc.
    Doe|Jill|answer1|answer2|

    Currently it shows in the this format:

    Doe|John|Question1|answer1
    Doe|John|Question2|answer2
    Doe|Jill|Question1|answer1
    Doe|Jill|Question 2|answer2

    Here is my query. I just need to know how to just get started and I can probably do the rest.

    SELECT Distinct

    dbo.uvwReporting_SurveyAnswers.DateCreated AS DateCreated

    ,dbo.uvwReporting_SurveyAnswers.questionid AS QuestionID

    ,dbo.uvwReporting_SurveyAnswers.userid AS UserID

    ,dbo.uvwReporting_SurveyAnswers.surveyid AS SurveyID

    ,dbo.uvwReporting_User.LastName1 AS LastName

    ,dbo.uvwReporting_User.FirstName AS FirstName

    ,dbo.uvwReporting_SurveyQuestions.ordernumber AS OrderNumber

    ,dbo.uvwReporting_SurveyQuestions.QuestionText AS QuestionText

    ,dbo.uvwReporting_SurveyAnswers.QuestionAnswer AS QuestionAnswer


    FROM

    dbo.uvwReporting_Surveys

    INNER JOIN dbo.uvwReporting_SurveyQuestions

    ON dbo.uvwReporting_Surveys.surveyid = dbo.uvwReporting_SurveyQuestions.surveyid

    INNER JOIN dbo.uvwReporting_SurveyAnswers

    ON dbo.uvwReporting_SurveyQuestions.QuestionID = dbo.uvwReporting_SurveyAnswers.QuestionID

    INNER JOIN dbo.uvwReporting_User

    ON dbo.uvwReporting_SurveyAnswers.userid = dbo.uvwReporting_User.userid

    WHERE

    dbo.uvwReporting_SurveyAnswers.surveyid = 100

    Order by dbo.uvwReporting_SurveyAnswers.DateCreated

    ,dbo.uvwReporting_SurveyQuestions.ordernumber

    Thanks

    MickeyD

  2. #2
    Join Date
    Jun 2003
    Location
    Ohio
    Posts
    12,592
    Provided Answers: 1
    Read the section on CROSSTAB queries in Books Online, and it will explain how to use CASE statements to do this.
    If it's not practically useful, then it's practically useless.

    blindman
    www.chess.com: "sqlblindman"
    www.LobsterShot.blogspot.com

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •