I have a table with films and the dates they were shown on along with other info in other columns, in
So relevant columns are...
FilmID FilmName DateShown
The dates are stored as
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?
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
<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