Bind where clause to a column

Input:

When Process ID = 610

ID	Value
50	2
60	16
//	2
80	128

I have two separate queries that fetch two values from the same table so I am trying to merge them together instaed of calling the table twice.

select [serialno],[storedatetime], [value1], [value2], 
    ROW_NUMBER() OVER (PARTITION BY [serialno] ORDER BY [storedatetime] desc) AS RowNumber 
    from [database] 
    where [processid] = 610 and [stepid] = 50

When this is run the where clause applies to [value1] and nothing is set for [value2] I am trying to make a different where clause to apply to [value2] where the [stepid] is a different number like:

  where [processid] = 610 and [stepid] = 80

This is quite new to me and haven’t managed to find a way yet. Any help is appreciated.

Output:

Answer

Do you need in this:

SELECT [serialno],
       [storedatetime], 
       MAX(CASE WHEN [stepid]= 50 THEN {some_column_name} END) [value1],
       MAX(CASE WHEN [stepid]= 80 THEN {some_column_name} END) [value2],
       ROW_NUMBER() OVER (PARTITION BY [serialno] ORDER BY [storedatetime] desc) AS RowNumber 
FROM [database] 
WHERE [processid] = 610 and [stepid] IN (50, 80)
GROUP BY 1, 2