In this tutorial, we will learn How can I return Pivot table output in MySQL although we do not have a pivot function in MySQL as some other database has we can always achieve the same using some other method which could be the same as of pivot function using aggregate function and conditional function.
Where we know that pivot and unpivot are inverse operations in SQL. Which is used to transform columns into rows and when required rows into columns, Or we can also conclude it as it is used for aggregate data along a certain axis.
Pivot Table
As we need to convert the rows of a table into columns and columns into a row and we can do so using a process called pivot which we perform using aggregate the function the example of the same is given below which we can follow for getting what exactly it is and how does it works with a pseudo example of code given below:
SELECT *
FROM (SELECT StudentID, Subject, Score
FROM (SELECT StudentID, Math, Science, English
FROM grades) p
UNPIVOT (Score FOR Subject IN (Math, Science, English)) AS unpvt) AS t
PIVOT (AVG( Score) FOR Subject IN (Math, Science, English)) AS pvt;
The above code will result in the table into the following results where we have to take the average of each column and using
StudentID Math_AVG Science_AVG English_AVG 1 80 90 85 2 75 85 80 3 90 80 90
Where are original table was as followed:
StudentID Subject Score 1 Math 80 1 Science 90 1 English 85 2 Math 75 2 Science 85 2 English 80 3 Math 90 3 Science 80 3 English 90
PIVOT and UNPIVOT are inverse operations in SQL that are used to transform data between a wide and narrow format. UNPIVOT is used to convert columns into rows, while PIVOT is used to convert rows into columns.
Here we have discussed how pivot works and for unpivot follow the code below to make the table as it was before in other words we can say how to inverse the table as it was before.
SELECT StudentID, Subject, Score
FROM (SELECT StudentID, Math, Science, English
FROM grades) p
UNPIVOT (Score FOR Subject IN (Math, Science, English)) AS unpvt;
The above code will be used for reversing the table in the same format as it was before.
To learn more about the pivot function in SQL table where we change the format and data of table by changing the data of row into column and column into row visit: Pivot working in SQL for interchanging the row with the column.
To learn more about MySQL Solutions and tutorials for the problems faced during learning MySQL databases visit: MySQL Tutorials And Problems.
