x

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

avatar image

swethaashwini
223 18 18 23

(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

avatar image

malpashaa
404 1 2 5

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.

Follow this question

By Email:

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

By RSS:

Answers

Answers and Comments

SQL Server Central

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

Topics:

x1146
x1066

asked: Feb 04, 2011 at 10:31 AM

Seen: 912 times

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

Copyright 2016 Redgate Software. Privacy Policy