Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
a2l2en
Starting Member
11 Posts |
 Posted - 2007-10-16 : 19:08:57
|
| I have surveys that I need to add weights to and was wondering if there was a way to convert the contents of a column.select empid, ans_for_ql, (if ans_for_ql = A then 0, B then 3, C then 5) as weightscore, ans_for_q2,(if ans_for_q2 = A then 8, B then 4, C then 2, D then 1, E then 0) as weightscoreHere is what the table looks like:empid | Ans_for_Q1 | Ans_for_Q2 1001 A C 1002 B E And these are the possible answers and what they need to be converted to:Weights for answers to Question1Q1_A = 0Q1_B = 3Q1_C = 5Weights for answers to Question2Q2_A = 8Q2_B = 4Q2_C = 2Q2_D = 1Q2_E = 0 |
|
|
Kristen
Test
22859 Posts |
Posted - 2007-10-17 : 00:43:59
|
| (if ans_for_ql = A then 0, B then 3, C then 5) as weightscoreneeds to be something like:CASE ans_for_ql WHEN 'A' then 0 WHEN 'B' then 3 WHEN 'C' then 5 END as weightscoreKristen |
 |
|
|
a2l2en
Starting Member
11 Posts |
Posted - 2007-10-17 : 11:16:33
|
| The following case worked:SELECT empid, Score1 = 0.05, ms.question1 as Answer_1, CASE WHEN ms.question1 = 'a' THEN 8 WHEN ms.question1 = 'b' THEN 5 WHEN ms.question1 = 'c' THEN 2 else 'error' END as weightscorefrom tablewhere ...Thanks Kristen!! |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2007-10-17 : 12:21:58
|
be careful that you are mixing numbers and strings in the CASE statement (8, 5, 2, "error"). SQL either won;t like the "error" if it springs up, or will treat all the others as Strings - which may have unexpected side effects.NULL is a good choice for "other" - so long as you catch it later on - as it is happy being any datatype  |
|
|
|
|
|
|
|
|
Calculating Survey Scores