Results 1 to 2 of 2
  1. #1
    Join Date
    Aug 2003
    Posts
    1

    Question Unanswered: Transform row data to column data

    Hi, Any help will be appreicated.

    I have a table with three fields: userid, questionid, answer.
    questionid will range from 1-37. so one user might have 37 entries at the most.

    Now I want to create a view with these fields based on data provide by the table I mentioned above:
    userid, answerforquestion1, answerforquestion2......answerforquestion37

    Any idea how to do it?

    Thanks,

  2. #2
    Join Date
    Dec 2002
    Posts
    63
    Well, this sounds like a homework project. If it is, please try and understand this solution before turning it in. If it isnt, then rock on and use this as you will. This assumes that the base table name is temp and creates a view called test_vw

    Code:
    CREATE VIEW test_vw
    
    AS
    
                SELECT            userid,
    
                            MAX(CASE WHEN questionid = 1 THEN answer else '' END) as q01,
    
                            MAX(CASE WHEN questionid = 2 THEN answer else '' END) as q02,
    
                            MAX(CASE WHEN questionid = 3 THEN answer else '' END) as q03,
    
                            MAX(CASE WHEN questionid = 4 THEN answer else '' END) as q04,
    
                            MAX(CASE WHEN questionid = 5 THEN answer else '' END) as q05,
    
                            MAX(CASE WHEN questionid = 6 THEN answer else '' END) as q06,
    
                            MAX(CASE WHEN questionid = 7 THEN answer else '' END) as q07,
    
                            MAX(CASE WHEN questionid = 8 THEN answer else '' END) as q08,
    
                            MAX(CASE WHEN questionid = 9 THEN answer else '' END) as q09,
    
                            MAX(CASE WHEN questionid = 10 THEN answer else '' END) as q10,
    
                            MAX(CASE WHEN questionid = 11 THEN answer else '' END) as q11,
    
                            MAX(CASE WHEN questionid = 12 THEN answer else '' END) as q12,
    
                            MAX(CASE WHEN questionid = 13 THEN answer else '' END) as q13,
    
                            MAX(CASE WHEN questionid = 14 THEN answer else '' END) as q14,
    
                            MAX(CASE WHEN questionid = 15 THEN answer else '' END) as q15,
    
                            MAX(CASE WHEN questionid = 16 THEN answer else '' END) as q16,
    
                            MAX(CASE WHEN questionid = 17 THEN answer else '' END) as q17,
    
                            MAX(CASE WHEN questionid = 18 THEN answer else '' END) as q18,
    
                            MAX(CASE WHEN questionid = 19 THEN answer else '' END) as q19,
    
                            MAX(CASE WHEN questionid = 20 THEN answer else '' END) as q20,
    
                            MAX(CASE WHEN questionid = 21 THEN answer else '' END) as q21,
    
                            MAX(CASE WHEN questionid = 22 THEN answer else '' END) as q22,
    
                            MAX(CASE WHEN questionid = 23 THEN answer else '' END) as q23,
    
                            MAX(CASE WHEN questionid = 24 THEN answer else '' END) as q24,
    
                            MAX(CASE WHEN questionid = 25 THEN answer else '' END) as q25,
    
                            MAX(CASE WHEN questionid = 26 THEN answer else '' END) as q26,
    
                            MAX(CASE WHEN questionid = 27 THEN answer else '' END) as q27,
    
                            MAX(CASE WHEN questionid = 28 THEN answer else '' END) as q28,
    
                            MAX(CASE WHEN questionid = 29 THEN answer else '' END) as q29,
    
                            MAX(CASE WHEN questionid = 30 THEN answer else '' END) as q30,
    
                            MAX(CASE WHEN questionid = 31 THEN answer else '' END) as q31,
    
                            MAX(CASE WHEN questionid = 32 THEN answer else '' END) as q32,
    
                            MAX(CASE WHEN questionid = 33 THEN answer else '' END) as q33,
    
                            MAX(CASE WHEN questionid = 34 THEN answer else '' END) as q34,
    
                            MAX(CASE WHEN questionid = 35 THEN answer else '' END) as q35,
    
                            MAX(CASE WHEN questionid = 36 THEN answer else '' END) as q36,
    
                            MAX(CASE WHEN questionid = 37 THEN answer else '' END) as q37
    
                FROM test
    
                GROUP BY userid
    
    GO
    This assumes that the questionID is a number between 1 and 37 ,that there are no more than 37 questions, and that there is a one to many relationship between userid and questionid. If you would like other ways to solve the problem, look into cross-tab queries.

Posting Permissions

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