How to write CASE Statement for new column for multiple values in other column?

I am writing a T-SQL code to create a new column based on condition of values in other column.

Understand it with example:
I have a table named [dbo].[tr] and values in this table are below –

Next, in col1, I have IDs and in col2, I have values. For some IDs, I have multiple values and for some IDs I have single values (either ‘X’ or ” (Empty string) ).

I want to create a new column (Using CASE Statement) based on the below conditions:

1. If value in col2 is ‘X’ then ‘Closed’
2. If value in col2 is ” then ‘Open’ ( ” means Empty string)
3. And if a ID have more than on one value, And for that ID if at least value in col2 is ” (Empty String) then it is ‘Open’

Right answer is attached below

>Solution :

For this dataset, you can use window functions:

select col1, col2,
    min(col2) over(partition by col1) open_closed
from tr

This works because, string-wise, '' < 'X' – so computing the min of col2 across rows having the same col1 value is good enough.

A more generic approach, that does not depend on a string sort, would use a case expression:

select col1, col2,
    max(case when col2 = '' then 1 else 0 end) over(partition by col1) as is_open
from tr

I find that 1/0 values are more appropriate that open/close strings – but you can change that:

select col1, col2,
    case when max(case when col2 = '' then 1 else 0 end) over(partition by col1) = 1
        then 'open' else 'closed' end as is_open
from tr

Or, using the fact that 'open' > 'closed'

select col1, col2,
    max(case when col2 = '' then 'open' else 'closed' end) 
        over(partition by col1) open_closed
from tr