SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 case
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

kt
Yak Posting Veteran

75 Posts

Posted - 10/30/2013 :  12:05:40  Show Profile  Reply with Quote
Hi,

The region table is stored the values for:
regionName
============
UNITED STATES
2
INDIA
3
HONG KONG
1
4

if regionName has the value as #, then I want to replace that number with the name, else just keep the name (UNITED STATES,INDIA,HONG KONG)

select [customer],
case regionName
when 1 then 'Asia Pacific'
when 2 then 'Europe, Middle East, & Africa'
when 3 then 'North America'
when 4 then 'South America'
else regionName
END AS region
from [dbo].[region]

thanks

James K
Flowing Fount of Yak Knowledge

3323 Posts

Posted - 10/30/2013 :  12:27:47  Show Profile  Reply with Quote
quote:
Originally posted by kt

Hi,

The region table is stored the values for:
regionName
============
UNITED STATES
2
INDIA
3
HONG KONG
1
4

if regionName has the value as #, then I want to replace that number with the name, else just keep the name (UNITED STATES,INDIA,HONG KONG)

select [customer],
case regionName
when 1 then 'Asia Pacific'
when 2 then 'Europe, Middle East, & Africa'
when 3 then 'North America'
when 4 then 'South America'
else regionName
END AS region
from [dbo].[region]

thanks

Is that query not working? If not, try this:
SELECT  [customer] ,
        CASE WHEN regionName = '1' THEN 'Asia Pacific'
             WHEN regionName = '2' THEN 'Europe, Middle East, & Africa'
             WHEN regionName = '3' THEN 'North America'
             WHEN regionName = '4' THEN 'South America'
             ELSE regionName
        END AS region
FROM    [dbo].[region]
Go to Top of Page

Grifter
Constraint Violating Yak Guru

251 Posts

Posted - 10/30/2013 :  12:40:08  Show Profile  Reply with Quote
I replicated that and it appeared to work:

select case t.Region 
when '1' then 'Asia Pacific'
when '2' then 'Europe, Middle East, & Africa'
when '3' then 'North America'
when '4' then 'South America'
else t.Region
end as region
from TempRegion t



region
=============================
UNITED STATES
Europe, Middle East, & Africa
INDIA
North America
HONG KONG
Asia Pacific
South America


Go to Top of Page

kt
Yak Posting Veteran

75 Posts

Posted - 10/30/2013 :  13:40:59  Show Profile  Reply with Quote
worked, thanks all !!!!

Edited by - kt on 10/30/2013 13:42:54
Go to Top of Page

madhivanan
Premature Yak Congratulator

India
22713 Posts

Posted - 10/31/2013 :  03:00:43  Show Profile  Send madhivanan a Yahoo! Message  Reply with Quote
If there are more number of regions, you can have a mapping table with region codes and names and join with source table to get the names

Madhivanan

Failing to plan is Planning to fail
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.05 seconds. Powered By: Snitz Forums 2000