Sivakrishna Kuragayla Sivakrishna Kuragayla - 1 year ago 95
SQL Question

Case statement in SQL server

I have table with the data as shown below,

create table #UserRegion (
UserName varchar(25),
RegionName varchar(25),
RegionID varchar(10),
RegionStatus varchar(15),
DefaultFlag char(1)

Insert into #UserRegion
values ('UserOne','Chicago','MW-1','Oniline','1')

i want the result set as, if regionname/regionid null then it should the pick the regionname and region id from available regionname and regionid where Defaultflag =1.
Any help will be appreciated.

Answer Source

You would appear to have a very poor data structure. Why are the defaults stored with the users?

In any case, this might do what you want:

select ur.*,
       coalesce(ur.regionname, urdefault.regionname) as regionname,
       coalesce(ur.regionid, urdefault.regionid) as regionid
from #UserRegion ur cross join
     (select ur.*
      from #UserRegion ur
      where DefaultFlag = '1'
     ) urdefault;

This assumes that only one row has the default flag set.

I should note that you can do this without a subquery:

select ur.*,
                max(case when DefaultFlag = '1' then regionname end) over ()
               ) as regionname,
                max(case when DefaultFlag = '1' then regionid end) over ()
               ) as regionid
from #UserRegion ur;