Sivakrishna Kuragayla Sivakrishna Kuragayla - 1 year ago 74
SQL Question

Trying to use 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'),
('UserTwo', 'SanJose', 'W-6', 'Oniline', '0'),
('UserThree', NULL, NULL, 'Oniline', NULL),
('UserFour', NULL, NULL, 'Oniline', NULL),
('UserFive', 'Miami', 'E-4', 'Oniline', '0')

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;
Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download