March 21, 2013 at 12:43 pm
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
March 21, 2013 at 12:53 pm
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/
March 21, 2013 at 1:06 pm
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
March 21, 2013 at 2:09 pm
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/
March 21, 2013 at 2:48 pm
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.
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
This website stores cookies on your computer.
These cookies are used to improve your website experience and provide more personalized services to you, both on this website and through other media.
To find out more about the cookies we use, see our Privacy Policy