How to select a column based on the value of another column

  • If I have a table that has:

    id | answer1 | answer2 | answer3 | answer4 | answer

    1 abc efg klm rst 2

    2 abc efg klm rst 3

    How can I get the answer based on the column name e.g.

    for id=1 needs to retun me 'efg' because the answer is stored in answer2

    for id=2 needs to retun me 'klm' because the answer is stored in answer3

    Thank you

  • markcrosbie (3/21/2013)


    If I have a table that has:

    id | answer1 | answer2 | answer3 | answer4 | answer

    1 abc efg klm rst 2

    2 abc efg klm rst 3

    How can I get the answer based on the column name e.g.

    for id=1 needs to retun me 'efg' because the answer is stored in answer2

    for id=2 needs to retun me 'klm' because the answer is stored in answer3

    Thank you

    You could use a case expression.

    Something like:

    case answer

    when 1 then answer1

    when 2 then answer 2

    when 3 then answer 3

    when 4 then answer 4

    end

    It would honestly be FAR better to normalize your data. Then you don't have to fight the architecture so much. This sort of looks like a multiple choice question with the answer shoved into the same row?

    _______________________________________________________________

    Need help? Help us help you.

    Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

    Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

    Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
    Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
    Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
    Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

  • Yeah, you right, this table should be normalized but this is what I get.

    The other problem is that the number of columns could also change having more answers. I was hoping to do some dynamic query whereby I can select the correct column based on the value stored in the answer column

  • markcrosbie (3/21/2013)


    Yeah, you right, this table should be normalized but this is what I get.

    The other problem is that the number of columns could also change having more answers. I was hoping to do some dynamic query whereby I can select the correct column based on the value stored in the answer column

    Well you could build dynamic sql to overcome the lack of normalization but that is really dealing with the symptom instead of the problem. I would NOT recommend that approach.

    I know what you mean that sometimes you just have to make it work but there are times you have to convince business that they have to stop doing everything right now, and instead do something right.

    If this were properly normalized the numbers of possible answers would not matter at all because the query results work with any number of answers.

    _______________________________________________________________

    Need help? Help us help you.

    Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

    Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

    Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
    Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
    Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
    Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

  • I'm agree with Sean here. Dynamic SQL has it's place as an optimization tool, particularly when dealing with parameterization and sniffing.

    Doing dynamic SQL against a dynamic SCHEMA screams so much ORM that my spine is currently rearranging itself into a pretzel.

    If this is a third party app, create your own database and normalize the data in a warehouse. If it's in house, go beat the devs to do things properly.


    - Craig Farrell

    Never stop learning, even if it hurts. Ego bruises are practically mandatory as you learn unless you've never risked enough to make a mistake.

    For better assistance in answering your questions[/url] | Forum Netiquette
    For index/tuning help, follow these directions.[/url] |Tally Tables[/url]

    Twitter: @AnyWayDBA

Viewing 5 posts - 1 through 4 (of 4 total)

You must be logged in to reply to this topic. Login to reply