sql server - Binary filter for max value of SQL query -

i using microsoft sql server management studio. new sql , not know how start going solving apologize in advance lack of attempted solutions. simplified example of current sql query follows:

use [database] go select [id]       ,[value1]       ,[max1]       ,[value2]       ,[max2] [datatable] 

which yields so:

[id] [value1] [max1] [value2] [max2] [1]    [2]     [0]      [9]     [1] [1]    [4]     [0]      [3]     [0] [1]    [8]     [1]      [5]     [0] [1]    [5]     [0]      [6]     [0] [2]    [1]     [0]      [1]     [0] [2]    [6]     [1]      [4]     [0] [2]    [3]     [0]      [3]     [0] [2]    [2]     [0]      [5]     [1] 

i trying datatable (datatable) select value corresponding binary indicator (max1 , max2 columns value1 , value2 respectively) maximum value id. trying above table become:

[id] [value1] [value2] [1]    [8]      [9] [2]    [6]      [5] 

if simpler create separate data frame each variable (value1 in separate resultant data frame value2), fine well.

thank you

you can use conditional aggregation pick values corresponding binary values 1.

select  [id] ,max(case when [max1] = 1 [value1] end) value1 ,max(case when [max2] = 1 [value2] end) value2 [datatable] group [id]