SQLite3: merge rows with common columns

Question

For some context I have a table in SQLite3 that currently looks like this:

What I am looking to do is merge rows with the same breed. The same columns will not be populated in both cases. So far I have tried this kind of query but it doesn't really do the job I am looking for, as it will not deduplicate or merge the rows as desired. Also it seems to be difficult to generalise to all columns without having to manually type out each column name.

select distinct t1.breed, coalesce(t1.dog_group_1, t2.dog_group_1) from breed_merge t1 left join breed_merge t2 on t1.breed = t2.breed;

Output:

Afador|
Affenhuahua|
Affenpinscher|
Affenpinscher|GROUP 1 - TOYS
Afghan Hound|
Afghan Hound|GROUP 4 - HOUNDS
...

Desired output:

Afador|
Affenhuahua|
Affenpinscher|GROUP 1 - TOYS
Afghan Hound|GROUP 4 - HOUNDS
...

Where is that pipe in the output coming from? It neither appears in your sample data nor is it introduced in the query. — Tim Biegeleisen, Oct 10 '20 at 09:01
The pipe is how the SQLite3 console (by default) renders a column delimiter in column mode. — James Harrison, Oct 10 '20 at 09:02

score 1 · Accepted Answer · answered Oct 10 '20 at 09:14

For this sample data, where you have max 2 rows for each breed and each of these 2 rows (if they exist) contain a value or null, all you have to do is group by breed and use an aggregate function like MAX() for each of the other columns:

SELECT breed, MAX(imgsrc) imgsrc, MAX(dog_group_1) dog_group_1, .....
FROM breed_merge 
GROUP BY breed

SQLite3: merge rows with common columns

1 Answers1