| Author |
 Topic  |
|
|
cjcclee
Starting Member
33 Posts |
 Posted - 11/18/2013 : 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
Flowing Fount of Yak Knowledge
Germany
8781 Posts |
Posted - 11/18/2013 : 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 - 11/18/2013 : 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
Flowing Fount of Yak Knowledge
4614 Posts |
Posted - 11/18/2013 : 18:36:50
|
| What happens when you run Webfred's query on your data? |
|
|
|
visakh16
Very Important crosS Applying yaK Herder
India
52326 Posts |
Posted - 11/19/2013 : 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
|
|
|
| |
Topic |
|
|
|
help for query