Search code examples
sqlresultset

SQL: How to get result set containing single record for each user with existing value

The question can be misleading, I can explain the question here:

userId  col1    col2

001     null    null
001     1       null
002     1       1   
002     null    1
002     null    null
003     null    1
003     1       null

Final result of query

001     1   null
002     1   1
003     1   1

I have multiple records in a table for the user. Some columns contains the values and some don't. I want the final result as shown above. If there exists a value for any of the columns in any row for a user, I want that value in the final result set.

I hope the example above makes it clear.

Solution

  • Use group by aggregation with max:

    select userId, max(col1) as c1, max(col2) as c2
     from userTbl
    group by userId