case with multiple thens

Question

trying to get the below output .

Tables 1

condition : when the region is north america , need to change it to "America" but should also must have the new region id in the output.

Answer 1

;with cteRegions as (select max(case when Region = 'North America' then RegionId end) as OldRegionID,
max(case when Region = 'America' then RegionId end) as NewRegionID
from regions where Region in ('America', 'North America'))

select U.*, r.NewRegionID
from Users U inner join cteRegions R on U.RegionID = R.OldRegionID

Answer 2

Hi @Josh
-->
Not only 'North America' need to change to 'America', but also 'South America' , am i right?
If i understand right,then check this:

CREATE TABLE #Test1 (UserID VARCHAR(10),Uesr VARCHAR(10),RegionID VARCHAR(20),Region VARCHAR(20))  
INSERT INTO #Test1 VALUES  
('A863','Ryan','1E81E2CB','North America'),  
('E911','Bion','A82B30','South America'),  
('BE7C','Feeon','079D','America')  
CREATE TABLE #Test2(Region VARCHAR(20),RegionID VARCHAR(20))  
INSERT INTO #Test2 VALUES  
('America','079D'),('Japan','10A7AE'),('North America','1E81E2CB'),('South America','A82B30')  
  
;WITH CTE AS  
(  
 SELECT UserID,Uesr,RegionID  
       ,CASE WHEN Region='North America' THEN 'America'  
	         WHEN Region='South America' THEN 'America' ELSE Region END AS Region  
 FROM #Test1  
)  
SELECT UserID,Uesr,C.Region,T.RegionID AS New_RegionID   
FROM CTE C JOIN #Test2 T ON C.Region=T.Region  

Output:

Best regards,
LiHong

---

If the answer is the right solution, please click "Accept Answer" and kindly upvote it. If you have extra questions about this answer, please click "Comment".
Note: Please follow the steps in our Documentation to enable e-mail notifications if you want to receive the related email notification for this thread.