Solved

Sql server 2008  Development

Posted on 2011-03-01
5
388 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 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

Technology Partners: 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!

Question has a verified solution.

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

Suggested Solutions

In this article I will describe the Copy Database Wizard 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.
Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
Via a live example combined with referencing Books Online, show some of the information that can be extracted from the Catalog Views in SQL Server.
Via a live example, show how to extract insert data into a SQL Server database table using the Import/Export option and Bulk Insert.

733 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