[Webinar] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 406
  • Last Modified:

Sql server 2008 Development

I have a table like below

id    val    out
1     1
1    1
2    0
2    1
2    0
3    0
3    0
3    0


I need the out column to be updated based on below logic

id 1 has  val of 1 in both the rows  then the out column should be updated as 'True'

id 3 has  val of 0 in all  the rows  then the out column should be updated as 'False'

id 2  has  val of 0 and 1  in the rows  then the out column should be null


what I want is if  a particular id has val of 1 in all the rows I want it to be 'True' , If a particular id has val of 0 in all the
rows then I want out as 'false ' and if id has both the values then it should be null



id    val    out
1     1      True
1    1       True
2    0    
2    1
2    0
3    0      false
3    0      false
3    0

0
vijay11
Asked:
vijay11
2 Solutions
 
Aneesh RetnakaranDatabase AdministratorCommented:
update a set out = case when exists (select 1 from table1 b where a.id = b.id and b.val <> a.val ) then null
                              else case when a.val = 1 then 'true' else 'false' end end
from table1 a
0
 
KelmenCommented:
select id, val,
case
  when x.val = 0 and x.id_c = x1.id_cl then false
  when x.val = 1 and x.id_c = x1.id_cl then true
  else null
end
from
(
  select id, val, count(id) as id_c from x
  group by id, val
) as x

left join
(
  select id, count(id) as id_c from x
  group by id
) as x1
on x.id = x1.id
0
 
MeLindaJohnsonCommented:
CREATE FUNCTION [dbo].[getVAL]
  (@id int )
RETURNS varchar(10)
AS
BEGIN
  DECLARE @V1  INT
  declare @count int
  declare @out varchar(10)
  SET @V1 = 1

SELECT @V1 =  @V1 * VAL
            FROM TABLE1 WHERE ID = @ID

select @count = count(*)
from table1 where id = @id and val = 1
if @count > 0 and @v1 = 1
begin
      set @out = 'True'
end
if @count > 0 and @v1 = 0
begin
      set @out = 'Null'
end
if @count = 0 and @v1 = 0
begin
      set @out = 'False'
end

return(@out)
END

SELECT ID, VAL,DBO.GETVAL(ID) OUT FROM TABLE1
      
0
 
vijay11Author Commented:
Both the answers were fine . But I can assign only 500 points  combining both
0
 
MeLindaJohnsonCommented:
Out of curiousity, which one did you use?
0

Featured Post

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Tackle projects and never again get stuck behind a technical roadblock.
Join Now