MySQL DML operation-the best way to achieve pivot row transfer function

1. Background

* since MySQL does not support column-column conversion between the pivot feature of type Oracle and SQL Server.

two。 Table and data

Mysql > select * from t_temp +-+ | year | season | orderCount | +-+ | 2010 | first quarter | 100 | | 2010 | second quarter | 200 | 2010 | third quarter | | 2010 | fourth quarter | 400 | 2011 | first quarter | 150 | 2011 | second quarter | 300 | 2011 | third quarter | 450 | 2011 | fourth quarter | 600 | +-+ 8 rows in set (0.00 sec)

3. It is realized by subquery and case when judgment

Mysql > select year, sum (orderCount1) 'first quarter',-> sum (orderCount2) 'second quarter',-> sum (orderCount3) 'third quarter',-> sum (orderCount4) 'fourth quarter'-> from-> (- > select year) -> case when season = 'first quarter' then-> orderCount-> end orderCount1,-> case when season = 'second quarter' then-> orderCount-> end orderCount2 -> case when season = 'third quarter' then-> orderCount-> end orderCount3,-> case when season = 'fourth quarter' then-> orderCount-> end orderCount4-> from t_temp->) t-> group by year +-+ | year | first quarter | second quarter | third quarter | fourth quarter | +- -+ | 2010 | 100 | 200 | 300 | 400 | | 2011 | 150 | 300 | 450 | 600 | +-- -+ 2 rows in set (0.00 sec)

4. Realized by IF aggregate function

Mysql > SELECT year,-> SUM (IF (season = 'first quarter', orderCount, null)) AS 'first quarter',-> SUM (IF (season = 'second quarter', orderCount, null)) AS 'second quarter',-> SUM (IF (season = 'third quarter', orderCount, null) AS 'third quarter',-> SUM (IF (season = 'fourth quarter', orderCount) Null)) AS 'fourth quarter'-> FROM t_temp-> GROUP BY year +-+ | year | first quarter | second quarter | third quarter | fourth quarter | +- -+ | 2010 | 100 | 200 | 300 | 400 | | 2011 | 150 | 300 | 450 | 600 | +-- -+ 2 rows in set (0.00 sec)

5. Summary

In order to demand-driven technology, there is no difference in technology itself, only in business.