SQLTeam.com | Weblogs | Forums

Assign value to column with mutiple codes and nulls

Help assigning values to a field

Id,codes,flag
1,null,null
1,543,null
2,875,null
2,612,null
3,777,null
4,543,null

If a Id has multiple codes set the lower code to 1 and the other code to 0. If id has multiple codes but one code is null set it to 0 and the other to 1. Else all other claim is with single entries are 1

Example

Id,codes,flag
1,null,0
1,543,1
2,875,0
2,612,1
3,777,1
4,543,1

declare @t table (Id int,codes int)
insert into @t values
(1,null),
(1,543),
(2,875),
(2,612),
(3,777),
(4,543)


select id, Codes, case when Flag > 1 then 0 else Flag end as Flag
  from (
select * 
	, Row_number() over (partition by id order by Codes desc) as Flag
from @t) t
order by id, Codes

That's very clever (seriously) provided, of course, that changing the expected order of the output is OK. If the order is to be enforced as listed in the test data, then this problem can't actually be solved without the addition of a column that actually does enforce the order.