76967

SQL merge duplicate rows and join values that are different

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

Answer1:

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

<hr>

<strong>Comma-separated lists have a lot practical problems</strong>:

    <li>Can’t ensure that each value is the right data type: no way to prevent 1,2,3,banana,5</li> <li>Can’t use foreign key constraints to link values to a lookup table; no way to enforce referential integrity.</li> <li>Can’t enforce uniqueness: no way to prevent 1,2,3,3,3,5</li> <li>Can’t delete a value from the list without fetching the whole list.</li> <li>Can't store a list longer than what fits in the string column.</li> <li>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[[:>:]]'</li> <li>Hard to count elements in the list, or do other aggregate queries.</li> <li>Hard to join the values to the lookup table they reference.</li> <li>Hard to fetch the list in sorted order.</li> <li>Storing integers as strings takes about twice as much space as storing binary integers. Not to mention the space taken by the comma characters.</li> </ul> <hr>

    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

Recommend

  • Listing functional dependencies - include transitive or multivalued?
  • Model normalization before model validation in Asp.Net Core 2.0+
  • XmlSerializer enumeration deserialization failing on (non existent) whitespace
  • Why the pattern matches one word while there is two identical word?
  • How to distribute an event to all nodes in a (Wildfly) cluster?
  • Allocating a 2D contiguous array within a function
  • Refactoring advice: maps to POJOs
  • Removing Duplicate Geometries
  • A class implementing two different IObservables?
  • can variables be set randomly when declaring them again?
  • Retrieving specified columns from a list of csv files to create a data data frame in R
  • How do I get the list of bad records that didn't load in Bigquery?
  • Cloud Code function running twice
  • How to override value that appears in a dropdown in the rails_admin gem
  • WPF version of .ScaleControl?
  • bad substitution shell- trying to use variable as name of array
  • Assign variable to the value in HTML
  • ActiveRecord query for a count of new users by day
  • Can you perform a UNION without a subquery in SQLAlchemy?
  • Bad request using file_get_contents for PUT request in PHP
  • PostgreSQL Query without WHERE only ORDER BY and LIMIT doesn't use index
  • MongoDB in PHP using aggregate to group by _id is null not working
  • How do I change content of ComboFieldEditor?
  • Finding past revisions of files in StarTeam w/ .NET SDK / C#
  • Java applet as stand-alone Windows application?
  • Why doesn't :active or :focus work on text links in webkit? (safari & chrome)
  • Display Images one by one with next and previous functionality
  • Weird JavaScript statement, what does it mean?
  • jQuery tmpl and DataLink beta
  • Do I've to free mysql result after storing it?
  • Jquery - Jquery Wysiwyg return html as a string
  • Return words with double consecutive letters
  • Comma separated Values
  • Android Studio and gradle
  • InvalidAuthenticityToken between subdomains when logging in with Rails app
  • Proper way to use connect-multiparty with express.js?
  • How to set the response of a form post action to a iframe source?
  • LevelDB C iterator
  • Can't mass-assign protected attributes when import data from csv file
  • Reading document lines to the user (python)