question

Josh-0762 avatar image
0 Votes"
Josh-0762 asked LiHongMSFT-3908 commented

case with multiple thens

trying to get the below output .

Tables 1

201582-image.png


201547-image.png



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.


201602-image.png


sql-server-transact-sql
image.png (8.5 KiB)
image.png (2.9 KiB)
image.png (7.7 KiB)
image.png (5.4 KiB)
· 1
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

Hi @Josh-0762
Is this issue solved now? If you have any questions, please feel free to share with us.
And don't forget to accept the answers if they helped. Your action would be helpful to other users who encounter the same issue and read this thread.
Thanks for your understanding!

Best regards,
LiHong

0 Votes 0 ·
NaomiNNN avatar image
0 Votes"
NaomiNNN answered

;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

5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.

LiHongMSFT-3908 avatar image
0 Votes"
LiHongMSFT-3908 answered

Hi @Josh-0762
201644-image.png -->201607-image.png
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:
201608-image.png

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.


image.png (5.2 KiB)
image.png (4.3 KiB)
image.png (3.4 KiB)
5 |1600 characters needed characters left characters exceeded

Up to 10 attachments (including images) can be used with a maximum of 3.0 MiB each and 30.0 MiB total.