Solved

Sql server 2008  Development

Posted on 2011-03-01
5
354 Views
Last Modified: 2012-05-11
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
Comment
Question by:vijay11
5 Comments
 
LVL 75

Accepted Solution

by:
Aneesh Retnakaran earned 300 total points
ID: 35013078
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
 
LVL 5

Assisted Solution

by:Kelmen
Kelmen earned 200 total points
ID: 35013802
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
 
LVL 4

Expert Comment

by:MeLindaJohnson
ID: 35013904
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
 

Author Closing Comment

by:vijay11
ID: 35020222
Both the answers were fine . But I can assign only 500 points  combining both
0
 
LVL 4

Expert Comment

by:MeLindaJohnson
ID: 35020359
Out of curiousity, which one did you use?
0

Featured Post

How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

Join & Write a Comment

In this article I will describe the Detach & Attach method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
In this article we will get to know that how can we recover deleted data if it happens accidently. We really can recover deleted rows if we know the time when data is deleted by using the transaction log.
Using examples as well as descriptions, and references to Books Online, show the documentation available for date manipulation functions and by using a select few of these functions, show how date based data can be manipulated with these functions.
Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.

743 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

13 Experts available now in Live!

Get 1:1 Help Now