SQL merge duplicate rows and join values that are different

Question

I have a table with films and the dates they were shown on along with other info in other columns, in MySQL

So relevant columns are...

FilmID

FilmName

DateShown

The dates are stored as Unix timestamps.

I currently have multiple instances of films that were shown on different dates yet all other information is the same. So I want to merge rows where the FilmName is the same but the DateShown is different, and join the DateShown fields into a comma separated list.

I've searched but struggled to find this exact query. Is it possible?

Also is it bad form in terms of database design to store the timestamps as a comma separated list?

Thanks

You have this problem because the schema is not normalized (decent keyword to google). But basically, you want one table for movies, and one separate table for views with a foreign key to the film table. That way the film info is only stored once. — Simon Lindgren, Apr 13 '15 at 07:28
It certainly is bad form to store comma separated values in a single field (breaches atomicity). Why are you trying to achieve that? How will you utilise such a field? — MatBailie, Apr 13 '15 at 07:28
@SimonLindgren thanks I was sort of expecting that (I've moved old MS Access DB to MySql and am trying to sort it out with some frustration). So is the best way for the dates to create a table with a DateID and a Date column and then another table linking the FilmId with the DateID?? — HowardHosk, Apr 13 '15 at 07:45
@HowardHosk That would give a many-to-many representation, ie you can say that on this time, these N movies were watched simultateously. Based on your question, I think a Film table and a Date table (for a lack of a better name) would be sufficient. That would only be able to represent many Dates per film, not many films per date. — Simon Lindgren, Apr 13 '15 at 14:00
I understand more now. So I create another table of filmdates with three columns FilmDateID FilmID Date and now I need to find the duplicate Film names and merge them but copy the dates across to the new table? — HowardHosk, Apr 13 '15 at 17:13
So I have figured out how to find all my duplicate FilmName records, but how do I write the SQL so that I can a) copy the FilmID of the first instance to the new Date table and b) add the Date field of the remaining instances to the new date table with the correct FilmID (i.e. the first instance) and then c) delete the duplicate rows leaving only the first instance — HowardHosk, Apr 13 '15 at 17:31

score 0 · Answer 1 · edited May 23 '17 at 12:14

Bad practice to use comma separated lists. You can read documentation about database normalization.

Comma-separated lists have a lot practical problems:

Can’t ensure that each value is the right data type: no way to prevent 1,2,3,banana,5
Can’t use foreign key constraints to link values to a lookup table; no way to enforce referential integrity.
Can’t enforce uniqueness: no way to prevent 1,2,3,3,3,5
Can’t delete a value from the list without fetching the whole list.
Can't store a list longer than what fits in the string column.
Hard to search for all entities with a given value in the list; you have to use an inefficient table-scan. May have to resort to regular expressions, for example in MySQL: idlist REGEXP '[[:<:]]2[[:>:]]'
Hard to count elements in the list, or do other aggregate queries.
Hard to join the values to the lookup table they reference.
Hard to fetch the list in sorted order.
Storing integers as strings takes about twice as much space as storing binary integers. Not to mention the space taken by the comma characters.

But If you need It for any case, you could use something like that:

SELECT  FilmID,
        FilmName, 
        Id = REPLACE(
                (
                SELECT DateShown AS [data()]
                FROM YourTable
                WHERE FilmID = a.FilmID
                ORDER BY FilmName FOR XML PATH('')), ' ', ','
                )
FROM    YourTable a
WHERE   FilmName IS NOT NULL
GROUP BY FilmID, FilmName

More about Comma-Separated lists

SQL merge duplicate rows and join values that are different

1 Answers1

Linked