CODEWITHSUNDEEP
sqlsqlitesql-merge
James Harrison
James Harrison

Reputation: 323

SQLite3: merge rows with common columns

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

DB Browser view of table breed_merge

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
...

Upvotes: 1

Views: 319

Answers (1)

forpas
forpas

Reputation: 164069

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

Upvotes: 1

Related Questions