asked on

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

ASKER CERTIFIED SOLUTION

Aneesh

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

SOLUTION

Kelmen

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

MeLindaJohnson

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

vijay11

ASKER

Both the answers were fine . But I can assign only 500 points combining both

MeLindaJohnson

Out of curiousity, which one did you use?