Results 1 to 2 of 2
  1. #1
    Join Date
    Jan 2011
    Posts
    9

    Unanswered: Rows to Columns?

    I am using SQL 2008 R2 and I have a table (Table1) that has two columns. AnswerID and OptionID.

    Ex:
    Code:
    AnswerID     OptionID
    1                1
    1                4
    1                7
    1                8
    2                1
    2                3
    2                8
    3                2
    3                4
    3                9
    I also have a Options table that has three columns OptionID, QuestionID and Value

    Let's say I want to check how many "ticks" each option for a specific question has got then I can write the following:
    Code:
    SELECT Count(*) Ticks FROM Table1 t INNER JOIN Options o ON t.OptionID = o.OptionID WHERE o.QuestionID = @QuestionID GROUP BY o.OptionID
    Now what if I wanted to make a filter where I only want to count "ticks" for each answer where OptionID 1 AND OptionID 4 has been chosen in the answer. I could write:
    Code:
    SELECT Count(*) Ticks FROM (SELECT * FROM Table1 WHERE AnswerID IN (SELECT t1.AnswerID FROM Table1 t1 INNER JOIN Table1 t2 ON t1.AnswerID = t2.AnswerID WHERE t1.OptionID = 1 AND t2.OptionID = 4)) t INNER JOIN Options o ON t.OptionID = o.OptionID WHERE o.QuestionID = @QuestionID GROUP BY o.OptionID
    Since the users will be able to create their own filters (he or she might want to filter on alot more options) the above T-SQL has to be dynamic. My question is if there is a better way to accomplish what I am trying to accomplish so that you can get better performance and that you don't have to make the T-SQL dynamic. With more advanced filters there could be lots and lots of JOINS otherwise.

    Thanks in advance.

  2. #2
    Join Date
    Jan 2011
    Posts
    9
    Is it not possible to accomplish this in a better way or was my question not clear enough?

Posting Permissions

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