Menu
How can I convert rows into columns in a MySQL query?
544
Unfortunately, MySQL does not have PIVOT function, so in order to rotate data from rows into columns you will have to use a CASE expression along with an aggregate function.
Some databases like Microsoft SQL Server or Oracle come with inbuilt functionality to create a pivot table using the inbuilt pivot() function. However, this function is not available in some databases such as MySQL and MariaDB.
A database table can store different types of data and sometimes we need to transform row-level data into column-level data. This problem can be solved by using the PIVOT() function. This function is used to rotate rows of a table into column values.
You can turn rows into a column with GROUP_CONCAT, but you can't transpose whole result sets in any automatic way. You either write a query that produces each column manually, or you do it in an application.
Here's a tutorial on writing the complicated queries to emulate the transposition:
http://www.artfulsoftware.com/infotree/queries.php#78
165
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
