Search code examples
sqlhiveimpala

Any way to achieve coalesce row wise?

I have a table 

| ID  |  V1  |  V2   |   
| 100 |  1   |  1    |
| 100 | null |  1    |
| 101 | null |  null |
| 101 |  1   |  1    |
| 102 |  1   |  null |
| 102 |  1   |  null |

Needed Sample output:

ID 100 has V1 value in at least one of the rows so need 1

same for ID 101 has V1 value in at least one of the rows so need 1

ID 102 has no V2 value in both rows so need null

Required output

| ID  |  V1  |  V2   |
| 100 |  1   |  1    |
| 101 |  1   |  1    |
| 102 |  1   |  null |

tried to combine the values into a list and get the max value

Is there any easier function which can achieve this?

Solution 
  • select ID, max(V1) as V1, max(V2) as V2 from table group by ID;