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

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:

If value in col2 is 'X' then 'Closed'
If value in col2 is '' then 'Open' ( '' means Empty string)
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

CodePudding user response：

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

CodePudding user response：

CASE MIN(col2) OVER (PARTITION BY Col1)
  WHEN '' THEN 'OPEN'
          ELSE 'CLOSED'
END