How to consolidate two rows

Question

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.

Answer 1

0

Try this:

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

answered Feb 04 '11 at 11:15 AM

malpashaa
404 ● 3

Thanks Malpashaa, it worked out perfectly. Could you please explain what happens when you we use Max(f1),max(f2)?

Feb 04 '11 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 '11 at 12:11 PM Oleg

@Oleg- Thanks

Feb 04 '11 at 12:37 PM swethaashwini

(comments are locked)

How to consolidate two rows

Follow this question

Related Questions