Recently whilst writing some code I needed to pivot rows to columns in a MySQL results set. Now with some other DB providers this is standard functionality. Oracle (of which I have previous experience of implementing pivot with), PostgreSQL and MS SQLServer all provide this. Unfortunately MySQL in it’s ultimate wisdom has never implemented this feature. This lead to one of two possibilities:
- Implement two separate queries and combine the results together in a PHP foreach loop
- As it supports them, a MySQL Stored Procedure
The first option was obviously not preferable, it’s far slower than getting the database to do the work. This lead to the second being my obvious preferred option. My main experience of writing stored procedures and functions in SQL is using PL/SQL with an Oracle DB. I have in general used MySQL on much lighter systems, however have in the odd situation required a stored procedure, so thought why not. The solution was to use a cursor in the stored procedure looping through said cursor to generate a SQL statement, then executing that as a prepared statement to return the results set required. This is then called from PHP by using the MySQL “CALL” functionality.
Here’s an example of the stored procedure I produced: