Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

Sql server 2008  Development

Posted on 2011-03-01
5
Medium Priority
?
404 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
5 Comments
 
LVL 75

Accepted Solution

by:
Aneesh Retnakaran earned 1200 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 800 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 Use the Help Bell

Need to boost the visibility of your question for solutions? Use the Experts Exchange Help Bell to confirm priority levels and contact subject-matter experts for question attention.  Check out this how-to article for more information.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In this article we will learn how to fix  “Cannot install SQL Server 2014 Service Pack 2: Unable to install windows installer msi file” error ?
It is possible to export the data of a SQL Table in SSMS and generate INSERT statements. It's neatly tucked away in the generate scripts option of a database.
Viewers will learn how to use the SELECT statement in SQL to return specific rows and columns, with various degrees of sorting and limits in place.
Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.

618 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