SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 SQL Server 2005 Forums
 Transact-SQL (2005)
 help for query
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

cjcclee
Starting Member

33 Posts

Posted - 11/18/2013 :  11:26:37  Show Profile  Send cjcclee an AOL message  Reply with Quote
Hi, all

I need help on this query.

ID1 yes
ID1 No
ID2 No
ID3 Yes

I want the result be:
ID1 Yes
ID2 No
ID3 Yes

each ID may have multiple yes/no, at least 1 yes, the result will be yes. I do not want cascade string, any better way to do it?Thanks!

webfred
Flowing Fount of Yak Knowledge

Germany
8760 Posts

Posted - 11/18/2013 :  11:37:51  Show Profile  Visit webfred's Homepage  Reply with Quote
declare @sample table (id varchar(30), col2 varchar(30))
insert @sample
select 'ID1', 'Yes' union all
select 'ID1', 'No' union all
select 'ID2', 'No' union all
select 'ID3', 'Yes'


select
id,
max(col2) as col2
from @sample
group by id



Too old to Rock'n'Roll too young to die.
Go to Top of Page

cjcclee
Starting Member

33 Posts

Posted - 11/18/2013 :  12:50:36  Show Profile  Send cjcclee an AOL message  Reply with Quote
Thanks for help. I may not say clearly, It is not always be Max(col).
each ID have multiple response for Yes/No, as long as one yes found, that ID will be yes. for example:

ID1 Yes
ID1 No
Id2 No
Id2 yes
Id3 No
Id4 Yes

The result need be
Id1 yes
id2 yes
Id3 no
Id4 yes



quote:
Originally posted by webfred

declare @sample table (id varchar(30), col2 varchar(30))
insert @sample
select 'ID1', 'Yes' union all
select 'ID1', 'No' union all
select 'ID2', 'No' union all
select 'ID3', 'Yes'


select
id,
max(col2) as col2
from @sample
group by id



Too old to Rock'n'Roll too young to die.

Go to Top of Page

Lamprey
Flowing Fount of Yak Knowledge

4608 Posts

Posted - 11/18/2013 :  18:36:50  Show Profile  Reply with Quote
What happens when you run Webfred's query on your data?
Go to Top of Page

visakh16
Very Important crosS Applying yaK Herder

India
52309 Posts

Posted - 11/19/2013 :  01:47:49  Show Profile  Reply with Quote
quote:
Originally posted by cjcclee

Thanks for help. I may not say clearly, It is not always be Max(col).
each ID have multiple response for Yes/No, as long as one yes found, that ID will be yes. for example:

ID1 Yes
ID1 No
Id2 No
Id2 yes
Id3 No
Id4 Yes

The result need be
Id1 yes
id2 yes
Id3 no
Id4 yes



quote:
Originally posted by webfred

declare @sample table (id varchar(30), col2 varchar(30))
insert @sample
select 'ID1', 'Yes' union all
select 'ID1', 'No' union all
select 'ID2', 'No' union all
select 'ID3', 'Yes'


select
id,
max(col2) as col2
from @sample
group by id



Too old to Rock'n'Roll too young to die.




the given suggestion would still work right
See illustration below


declare @sample table (id varchar(30), col2 varchar(30))
insert @sample
select 'ID1', 'Yes' union all
select 'ID1', 'No' union all
select 'ID2', 'No' union all
select 'ID2', 'Yes' union all
select 'ID3', 'No' union all
select 'ID4', 'Yes'


select
id,
max(col2) as col2
from @sample
group by id


output
---------------
id	col2
---------------
ID1	Yes
ID2	Yes
ID3	No
ID4	Yes



------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/
https://www.facebook.com/VmBlogs
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.05 seconds. Powered By: Snitz Forums 2000