I frequently jump between databases as lots of clients require data to be transferred between their old systems to their new systems. The two databases I work with most frequently are Microsoft SQL and MySQL. I like them both. In the constant battle for my devoted worship, MySQL wins today.

I’m currently working on a migration to Magento from a MySQL backed Rails site. When possible, I like to use Magento’s built-in import functionality due to its extremely complex table structure. I needed to get a comma-separated list of values representing the various images associated with a product.

The way it was in the original database was pretty straight forward – here’s a simplified version:

I needed to turn that into “1a.jpg;1b.jpg”. Now, in Microsoft SQL, you need to do some SQL acrobatics to make this happen (see this post). In MYSQL, this is cake:

SELECT product_id, GROUP_CONCAT(filename ORDER BY filename ASC SEPARATOR ';') AS 'filenames'
FROM product_images GROUP BY product_id

The results?

There’s nothing like a little simplicity.