How can I aggregate some tuples like this

COL_1 | COL_2 | COL_3 
 val  |   Abc   |   Null  
 val  |   Null  |   CDe 

with the OR function and return the following table?

COL_1 | COL_2 | COL_3 
 val  |   Abc   |   CDe 

How can I aggregate some tuples like this

COL_1 | COL_2 | COL_3 
 val  |   Abc   |   Null  
 val  |   Null  |   CDe 

with the OR function and return the following table?

COL_1 | COL_2 | COL_3 
 val  |   Abc   |   CDe 

• postgresql

Share Improve this question Follow edited Mar 21 at 10:27 Panagiotis Kanavos 132k1616 gold badges203203 silver badges265265 bronze badges asked Mar 21 at 10:26 Riya BansalRiya Bansal 1,31111 gold badge1111 silver badges1010 bronze badges 7

• OR doesn't aggregate. What do you mean by aggregation to begin with? What happens if there are more than 2 rows? MIN or MAX applied to the columns you posted would return the results you want for these 2 rows. What if there's a 3rd row, where COL_3 contains Efg? Do you want CDe or Efg in the result? – Panagiotis Kanavos Commented Mar 21 at 10:28
• There is a text field and I am converting rows into columns using case condition but multiples rows are coming instead of one and values in other column is coming as null – Riya Bansal Commented Mar 21 at 10:32
• For example for Boolean values I have used BOOL_OR function but for text value I am finding function – Riya Bansal Commented Mar 21 at 10:33
• There are no boolean values in the question. You haven't explained what you want yet, only how you think it would work. . Just MIN or MAX would produce the results you want, eg SELECT MIN(COL_1), MIN(COL_2), MIN(COL_3) FROM ThatTable. Functions like MIN and MAX eliminate NULLs – Panagiotis Kanavos Commented Mar 21 at 10:34
• 1 What is OR operation for strings? What you want for 'Klm' OR 'Opq'? 'KlmOpq'? – ValNik Commented Mar 21 at 10:35

 |  Show 2 more comments

1 Answer 1

Sorted by: Reset to default 0

I don't think OR is the right way to solve this since you must aggregate the records. Just use an aggregate function like MIN(), MAX(), ARRAY_AGG() or ANY_VALUE(). Even STRING_AGG() could be an option.

 SELECT     col_1
      , MIN(col_2) -- MAX() or ANY_VALUE() could also work
      , MIN(col_3)
 FROM (VALUES
           ('val','Abc',Null)
            ,('val',Null, 'CDe')
       ) t1(COL_1, COL_2, COL_3 )
 GROUP BY col_1
 ORDER BY col_1;