MySQL implements row and column conversions

Objective:

Recently played a SQL statement, think about it and give yourself a topic: "Row and column conversion." At first, I spent a lot of time surfing the web, but most of it was handled using Some convenient functions in the Oracle database, such as "pivot". So, what to do in a Mysql environment?

Give me a small example:

SqlCode implementation:

1 -- STEP1: Building tables and inserting data2 3 --STEP2: Intermediate conversion, that is, "two-dimensional to one-dimensional" to get a one-dimensional table. 4 5 --STEP3: The target result table can be obtained by using if judgment and "Group by". 6 7  8 9 --LoadingTen  One  Usetest; A  - Create TableScore ( -  theNamevarchar( One), -  -Mathint, -  +中文版int -  + ); A  at Insert  intoScoreValues('Armor', the, +); -  - Insert  intoScoreValues('b', the,Bayi); -  - Insert  intoScoreValues('C', the,98); -  in   -  to --Query generation target table +  - SELECT the  *C2 as 'Courses', $ Panax Notoginseng SUM(IF(C1='Armor', C3,0)) as 'Armor', -  the SUM(IF(C1='b', C3,0)) as 'b', +  A SUM(IF(C1='C', C3,0)) as 'C'  from( the  + SelectName asC1,'Math'  asC2,math asC3 fromScoreGroup  byname -  $ Union  $  - SelectName'中文版'  asC2,english fromScoreGroup   byname -  the) asTxGroup  byC2; - Wuyi   the 

Conclusion:

So far, individuals have not found a function in Mysql that makes it easy to quickly convert table rows and columns. Above is just a little bit of personal attempt, welcome to have a better solution to the friend criticism!

MySQL implements row and column conversions