sql - Tsql union query -

i’m looking efficient way query table. table structure is:

create table [dbo].[casemanager](     [caseid] [int] identity(1,1) not null,     [systemusercreatedby] [int] null,     [systemuserassignee] [int] null,      constraint [pk_case] primary key clustered  (     [caseid] asc )with (pad_index = off, statistics_norecompute = off, ignore_dup_key = off, allow_row_locks = on, allow_page_locks = on, fillfactor = 80) on [primary] ) on [primary]  

the query should return every caseid , userid (userid can either systemusercreatedby or systemuserassignee) bit columns show if use createdby or assignee managed write this:

select  caseid,userid,        max(caseuser.isassignee) isassignee,        max(caseuser.iscreator) iscreator       (   select cmassignee.caseid,          cmassignee.systemuserassignee userid,          1 isassignee ,          0 iscreator        casemanager cmassignee     cmassignee.systemuserassignee not null   union    select cmcreator.caseid,           cmcreator.systemusercreatedby userid,           0 isassignee ,           1 iscreator        casemanager cmcreator    cmcreator.systemusercreatedby not null     ) caseuser   group caseid,userid 

i’m pretty sure there better way write scanning table once .in example show 2 columns (systemusercreatedby systemuserassignee) have 5 need added.

please see example of data:

set identity_insert dbo.casemanager on; insert casemanager(caseid,systemusercreatedby,systemuserassignee) values (1,2222,3333) set identity_insert dbo.casemanager off; 

in case i’m looking :

caseid      userid      isassignee  iscreator ----------- ----------- ----------- ----------- 1           2222        0           1 1           3333        1           0  (2 row(s) affected) 

the caseid primary key, there's no need aggregation (if 1 of both columns not null):

select    caseid,    coalesce(systemuserassignee, systemusercreatedby) userid,    case when systemuserassignee not null 1 else 0 end isassignee,    case when systemusercreatedby not null 1 else 0 end iscreator casemanager cmassignee  

edit:

based on latest comments both columns can have data , might same user, original query ok (even if scans table twice), thing need change union all instead of union.

but 5 userid @amit's answer should best.