How to create a flag column that tells whether a column value varies within a group in SQL server-CodePudding

I want to create a flag column that tells whether the values in a certain columns are identical when they are grouped by another column. For example, the data looks like this:

ID         City       Code
AB123      London     CA1
AB123      New York   CA1
CD321      Paris      CA1
CD321      Tokyo      DW2

I'd like to add a new column that tells whether the values of CODE vary within a group when the data is grouped by ID.

ID         City       Code  Flag
AB123      London     CA1    0
AB123      New York   CA1    0
CD321      Paris      CA1    1
CD321      Tokyo      DW2    1

I tried to assign a row number by using partion by but it simply assins a row number by a group.

SELECT ID, City, Code, 
       ROW_NUMBER() OVER (PARTITION BY CODE, ID ORDER BY ID) as Flag
FROM table
ORDER BY ID

CodePudding user response：

You're right to consider a window function, if the minimum value in the window equals the maximum value in the window, they are the same:

select *,
  case when Min(code) over(partition by id)
  = max(code) over(partition by id) then 0 else 1 end Flag
from t;

CodePudding user response：

You could use MIN()/MAX() OVER() to compare the highest and lowest value of Code per ID:

SELECT ID, City, Code, Flag = CASE 
  WHEN MIN(Code) OVER (PARTITION BY ID) 
     = MAX(Code) OVER (PARTITION BY ID) THEN 0 ELSE 1 END
FROM dbo.YourTableName;

Working example in this fiddle.