Solved

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

Posted on 2010-11-30
5
533 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

Simplifying Server Workload Migrations

This use case outlines the migration challenges that organizations face and how the Acronis AnyData Engine supports physical-to-physical (P2P), physical-to-virtual (P2V), virtual to physical (V2P), and cross-virtual (V2V) migration scenarios to address these challenges.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Record open by another user 6 72
How to query LOCK_ESCALATION 4 42
Need help debbuging stored procedure 21 47
Please help for the below sql query. 1 29
This article will describe one method to parse a delimited string into a table of data.   Why would I do that you ask?  Let's say that you need to pass multiple parameters into a stored procedure to search for.  For our sake, we'll say that we wa…
I've encountered valid database schemas that do not have a primary key.  For example, I use LogParser from Microsoft to push IIS logs into a SQL database table for processing and analysis.  However, occasionally due to user error or a scheduled task…
Finds all prime numbers in a range requested and places them in a public primes() array. I've demostrated a template size of 30 (2 * 3 * 5) but larger templates can be built such 210  (2 * 3 * 5 * 7) or 2310  (2 * 3 * 5 * 7 * 11). The larger templa…
Attackers love to prey on accounts that have privileges. Reducing privileged accounts and protecting privileged accounts therefore is paramount. Users, groups, and service accounts need to be protected to help protect the entire Active Directory …

685 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