SQLite combine values of similar records into one

Question

In my SQLite database I have a table called Tracks which is made up of the following columns: artist, track, genre1, genre2, genre3. The table contains many values which have the same artist and track values with different genre1, genre2, genre3 values. Like the example below:

artist | track   | genre1  | genre2 | genre3
ABBA   | Song 1  | Rock    | Rock   |  Rock
U2     | Song 4  | Rock    | Rock   |  Rock
ABBA   | Song 1  | Pop     | Pop    |  Pop
U2     | Song 4  | Pop     | Pop    |  Pop
ABBA   | Song 1  | 70s     | 70s    |  70s
U2     | Song 4  | 90s     | 90s    |  90s

I need to create an SQLite statement that will amalgamate all unique genre values where the artist and track are the same, like the example shown below:

artist | track   | genre1  | genre2 | genre3
ABBA   | Song 1  | Rock    | Pop    |  70s
U2     | Song 4  | Pop     | Rock   |  90s

Any help with this would be hugely appreciated.

Answer 1

Unfortunately it looks like you are suffering from poor database design. In the design above you are limited to only 3 genre per song, and when only one genre is applicable to a song you just repeat the same value across all genre fields. In a good design you should be able to have as many or as little genre entries per song, as well you can also reduce the amount of data required to store genre values.

So lets talk many-to-many relations and mapping tables. A many-to-many relationship in this situation would be many unique genre values belonging to many unique song entries. This should also be true for the reverse, because we will also have many unique songs belonging to many (or possibly none) genres.

So how do we achieve this... Well first lets make a table called Genres. I should have two fields: a integer primary key, and a string for the genre value, with a unique constraint on the latter. Next we will need a mapping table (for futer reference lets call it SongGenre), a table that just has two foreign key fields, one for referencing the song table and one for referencing the genre table. Each record will say this genre belongs to this song, and we can have multiple entries per song.

once you get your mapping table setup you can achieve what you want to do with something like this

SELECT Artist, Track, Group_Concat(Genre.GenreName) 
FROM Song 
JOIN SongGenre USING (SongID) 
JOIN Genre USING (GenreID) 
GROUP BY Artist, Track;