Solved

How to use case in MSSQL with a list of values?

Posted on 2010-11-30
5
531 Views
Last Modified: 2012-05-10
I have a query where I need to assign a value depending on another set of values. For example...

column1
2
2
3
4
10
5

When rows in column 1 are equal to 2 or 3, I would like a column to be equal to "WT". All others should be "SP"... I wrote this:
case column1 when (2,3) then "WT" else "SP". Got a few syntax errors... Is this the correct way to write a CASE statement?
0
Comment
Question by:horalia
5 Comments
 
LVL 3

Expert Comment

by:alexbumbacea
ID: 34240787
SELECT case(column1)
when 1 then 'nt'
else 'unknown'

FROM table
0
 
LVL 3

Expert Comment

by:alexbumbacea
ID: 34240806
SELECT case(column1)
when column2 then 'wt'
when column3 then 'wt'
else 'sp'

FROM table

Sorry for previous. I haven't read the entire text.
0
 
LVL 6

Accepted Solution

by:
hyphenpipe earned 500 total points
ID: 34240834
select case when column_1 in (2,3) then 'wt' else 'sp' end
from table
0
 
LVL 5

Expert Comment

by:Vipul Patel
ID: 34240861
Below sample might be resolve your doubts;

DECLARE @Value INT=5

SELECT
CASE
WHEN @Value IN (2,3) THEN 'Vips'
WHEN @Value IN (6,7) THEN 'Patel'
ELSE 'BLANK'
END

please see attached modified code.

Andd visit below link for more information
http://msdn.microsoft.com/en-us/library/ms181765.aspx
SELECT 
CASE 
WHEN column1 IN (2,3) THEN 'WT'
ELSE 'SP'
END

Open in new window

0
 

Author Closing Comment

by:horalia
ID: 34241523
Exactly what I needed, thanks!
0

Featured Post

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Introduction: When running hybrid database environments, you often need to query some data from a remote db of any type, while being connected to your MS SQL Server database. Problems start when you try to combine that with some "user input" pass…
Use this article to create a batch file to backup a Microsoft SQL Server database to a Windows folder.  The folder can be on the local hard drive or on a network share.  This batch file will query the SQL server to get the current date & time and wi…
Nobody understands Phishing better than an anti-spam company. That’s why we are providing Phishing Awareness Training to our customers. According to a report by Verizon, only 3% of targeted users report malicious emails to management. With compan…
A short tutorial showing how to set up an email signature in Outlook on the Web (previously known as OWA). For free email signatures designs, visit https://www.mail-signatures.com/articles/signature-templates/?sts=6651 If you want to manage em…

778 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question