?
Solved

Sql server 2008  Development

Posted on 2011-03-01
5
Medium Priority
?
400 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

Percona Live Europe 2017 | Sep 25 - 27, 2017

The Percona Live Open Source Database Conference Europe 2017 is the premier event for the diverse and active European open source database community, as well as businesses that develop and use open source database software.

Question has a verified solution.

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

JSON is being used more and more, besides XML, and you surely wanted to parse the data out into SQL instead of doing it in some Javascript. The below function in SQL Server can do the job for you, returning a quick table with the parsed data.
In part one, we reviewed the prerequisites required for installing SQL Server vNext. In this part we will explore how to install Microsoft's SQL Server on Ubuntu 16.04.
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.
Via a live example, show how to extract information from SQL Server on Database, Connection and Server properties
Suggested Courses

762 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