How to check if there are multiple values for a column within a subsets of grouped records?

I am trying to identify when an order contains multiple status codes. An order number will have multiple order line records each with a status code, I need to know which orders have multiple different status codes among the different order lines. I am only concerned about ‘O’ and ‘C’ codes, but there are other codes as well.

In this example, OrderNum ‘1234’ contains lines with both ‘O’ and ‘C’ status. How do I systematically identify whether a given OrderNum satisfies this condition?

One method I’m exploring is to set up 2 columns for the order statuses ‘O’ and ‘C’ and then record for each line a 1 in the ‘O’ column if Order Status is O or a 1 in ‘A’ column if the order status is ‘A’. Then I would be able to group by the order number and sum ‘O’ and ‘C’ columns and use a logical operator that is Sum ‘O’ and Sum ‘A’ > 0 then the order would satisfy my condition.

Is there a simpler way to do this? Seems like something where there should be a well known solution, but no one on my team has a better idea how to accomplish this.

>Solution :

You should be able to use a simple aggregation:

select OrderNum
from t
where OrderStatus in ('O', 'C')
group by OrderNum
having Min(OrderStatus) != Max(OrderStatus);