sql server - IF THEN in T-SQL -

i working on db in t-sql , have problem now.

i have multiple rows same name different value in 1 of columns (in 1 row doc 0 , in second row doc 2000), , rows come once , 1 value.

now, want select ones have 0 in row, , if there no 0 want select 1 value.

here's sample data:

create table table1     ([name] varchar(3), [doc] int, [sum] int) ;  insert table1     ([name], [doc], [sum]) values     ('tom', 0, 100),     ('tom', 2000, 200),     ('jon', 2000, 200) ; 

now expect row tom has value of 100, , jon has value of 200.

one way, using row_number in cte:

with cte       (select [name],                  [doc],                  [sum],                  [rn] = row_number()                         over(                           partition name                           order case when doc=0 0 else 1 end)             table1)  select [name],         [doc],         [sum]    cte   rn = 1  

demo (with sample data)