SQL Server Case with conditional results

Question

0.00/5 (No votes)

See more:

SQL-Server-2008

What i'm trying to accomplish here is if response = 'N/A' then set response 1-9 to 'N/A' basically to hide those rows and display 'N/A'. The other scenario is if response = 'RevRank' then set response 1-9 to diplay response. If neither of these conditions are met then simply show responses. I'm not having any luck, anyone have any ideas on how to accomplish this?

SQL

SELECT Question, ResponseID,
CASE
    WHEN response IN ('N/A','1','2','3','4','5','6','7','8','9') THEN 'N/A'
    WHEN response IN ('RevRank','1','2','3','4','5','6','7','8','9') THEN response
    ELSE response
END AS response,
FROM view_PromonetProfileQuestionResponses 
WHERE promotionid = @promotionid

result set partial:
025 - Miscellaneous - 3666
13 State
HSI
Direct TV
Uverse Green
iPhone
iPad
LTE
Zodiac 3G
Non-Phone Plaque Color Dot Program
ISIS Trial
Wireless Home Phone
Lifeline
Digital Life
Dummy Phone
Priority 1
N/A
1
2
3
4
5
6
7
9

Posted 4-Apr-13 8:17am

JasonMacD

Updated 4-Apr-13 10:25am

v2

Add a Solution

Comments

Maciej Los 4-Apr-13 15:04pm

If i understand you well, you want to replace data stored in column response: values (1 to 9) to 'N/A' and 'RevRank' to (1 to 9), but on what condition???
Please, show me an example data, because till now, i'm a bit confused ;(

JasonMacD 4-Apr-13 16:27pm

So I added a result showing where N/A is selected and 1-9 is also, this would be where I want 1-9 to to be cloaked with N/A

JasonMacD 4-Apr-13 15:40pm

Yes basically it is dropdown lists and Im capturing the data. If RevRanks is selected I won't to display the 1-9 if 'N/A' is selected I want everything to show as 'N/A'. Keep in mind the response column is varchar not int because other reponses are words.

Maciej Los 4-Apr-13 16:15pm

Example data, please. Use "Improve question" widget to update your question (right-bottom corner of question).
To reply my comment, use "Reply" widget.

2 solutions

Solution 2

Try this:

SQL

SELECT Question, ResponseID, NewResponce =
CASE response
    WHEN '1' THEN 'N/A'
    WHEN '2' THEN 'N/A'
    WHEN '3' THEN 'N/A'
    WHEN '4' THEN 'N/A'
    WHEN '5' THEN 'N/A'
    WHEN '6' THEN 'N/A'
    WHEN '7' THEN 'N/A'
    WHEN '8' THEN 'N/A'
    WHEN '9' THEN 'N/A'
    ELSE response
END
FROM view_PromonetProfileQuestionResponses 
WHERE promotionid = @promotionid

Posted 4-Apr-13 11:07am

Maciej Los

Updated 4-Apr-13 11:11am

v2

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

fjdiewornncalwe · Accepted Answer · 2013-04-04T08:38:00

Solution 1

Try something like this:

SQL

SELECT  Question,
        Response AS 'RawResponse'
        ResponseID,
        CASE
           WHEN response = 'N/A' THEN 'N/A'
           WHEN response < 10 AND response > 0 THEN 'N/A'
           ELSE response
        END AS Response
  FROM  view_PromonetProfileQuestionResponses
 WHERE  promotionid = @promotionid

You had two columns returning as "Response", so I updated the first to "RawResponse".
As well, I just redid the case a little differently, but I would expect that yours should work as is as well.

Posted 4-Apr-13 8:38am

fjdiewornncalwe

Updated 4-Apr-13 8:39am

v2

Comments

JasonMacD 4-Apr-13 15:38pm

Thanks for the fast response (no pun intended), I didn't mean to put the response in there twice that was a typo. The reponse column is a varchar so the greater than less than is out of the question in this case.

fjdiewornncalwe 4-Apr-13 15:49pm

If you just keep your WHEN clause you're probably going to be OK with a single instance of the response column name. Cheers.