SQLTeam.com | Weblogs | Forums

Check condition in sql server

sql2008
sql2012

#1

I have doubt in sql server

source: emp

id      | name  | check 
100     |  a    |  1
100     |  b    |  2
100     |  c    |  3
100     |  d    |  5
101     |  a    |  1
101     |  b    |  5
102     |  a    |  1
102     |  b    |  2


here same id have check 2 and 5 values then we need to replace  check values to 2 check values for that id.
based on above table I want load/output data into target table like below 
target : emp1

id      | name  | check 
100     |  a    |  1
100     |  d    |  2
100     |  c    |  3
101     |  a    |  1
101     |  b    |  5
102     |  a    |  1
102     |  b    |  2

please tell me query how to achive this task in sql server


Need to Help query in sql server
Upda
#2

Something like this perhaps:

select a1.id
      ,a1.name
      ,isnull(a2.[check],a1.[check]) as [check]
  from emp as a1
       left outer join emp as a2
                    on a2.id=a1.id
                   and a1.[check] in (2,5)
                   and a2.[check] in (2,5)
                   and a2.[check]<>a1.[check]
 where a2.id is null
    or (a1.[check]=5
   and  a2.[check]=2
       )

Based on codittion retrive data in sql server
#3

Thanks you.