Ashley O Ashley O - 1 month ago 8
MySQL Question

SQL Query Help - Select non null values for each ID (multiple rows)

I have a table in BigQuery where each ID has multiple rows, many with Null values. I want to combine the rows to create a complete set.

Here is a sample set of data:

ID Address CreatedDate City
1 1 1st Street NULL New York City
1 NULL 8/18/17 NULL
2 NULL 8/13/17 Boston
2 2 2nd Street NULL NULL
3 3 3rd Street 8/1/17 Los Angeles
3 NULL NULL NULL
3 NULL 8/7/17 NULL


Here is the expected Output:

ID Address CreatedDate City
1 1 1st Street 8/18/17 New York City
2 2 2nd Street 8/13/17 Boston
3 3 3rd Street 8/1/17 Los Angeles


Honestly I'm not sure this is even possible, but any suggestions would be greatly appreciated!

Answer Source

Group by the column you want to be unique and use max() to get the results containing something for each id

select ID, max(Address), max(CreatedDate), max(City)
from your_table
group by ID