How to consolidate two rows

Hi All,

i have a select query which i use in Dataflow task, as shown below

select name,id,f1,f2
from (some table joins and conditions)
the output is 
name  id  f1  f2
a      1   no  yes
a      1   yes no
b      2    no   yes
b      2    no   no

now i want to consolidate this to one row as shown below. this depends on the condition that, if name and id are equal and then if f1 is "yes" for any one row (name + id combination) the output f1 should be "yes". Same for f2

name id   f1   f2
a    1     yes yes
b    2     no   yes

note: this should not change the underlying table data.

more ▼

asked Feb 04, 2011 at 10:31 AM in Default

swethaashwini gravatar image

223 17 18 20

(comments are locked)
10|1200 characters needed characters left

1 answer: sort voted first

Try this:

select name, id, MAX(f1) AS f1, MAX(f2) AS f2
from (some table joins and conditions)
group by name, id
more ▼

answered Feb 04, 2011 at 11:15 AM

malpashaa gravatar image

404 3

Thanks Malpashaa, it worked out perfectly. Could you please explain what happens when you we use Max(f1),max(f2)?
Feb 04, 2011 at 11:33 AM swethaashwini
When more than one row is aggregated into one, all the values in f1 are considered and the biggest is chosen. Because "yes" happens to begin with letter "y" it is bigger than "no" which begins with letter "n", so the max picks up value "yes". The same logic is for f2.
Feb 04, 2011 at 12:11 PM Oleg
@Oleg- Thanks
Feb 04, 2011 at 12:37 PM swethaashwini
(comments are locked)
10|1200 characters needed characters left
Your answer
toggle preview:

Up to 2 attachments (including images) can be used with a maximum of 524.3 kB each and 1.0 MB total.

New code box

There's a new way to format code on the site - the red speech bubble logo will automatically format T-SQL for you. The original code box is still there for XML, etc. More details here.

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here



Answers and Comments

SQL Server Central

Need long-form SQL discussion? SQLserverCentral.com is the place.



asked: Feb 04, 2011 at 10:31 AM

Seen: 801 times

Last Updated: Feb 04, 2011 at 10:31 AM