help for query - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2005 Forums

Transact-SQL (2005)

help for query

Author

Topic

cjcclee
Starting Member

33 Posts

Posted - 2013-11-18 : 11:26:37

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
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2013-11-18 : 11:37:51

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.

cjcclee
Starting Member

33 Posts

Posted - 2013-11-18 : 12:50:36

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.

Lamprey
Master Smack Fu Yak Hacker

4614 Posts

Posted - 2013-11-18 : 18:36:50

What happens when you run Webfred's query on your data?

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2013-11-19 : 01:47:49

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

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources