/* Horizontal table variable longitudinal table or Vertical table variable horizontal table
Score table below
Name Course Grade
Zhang San language 95
Zhang San Mathematics 90
Zhang San English 89
John Doe Language 92
John Doe Mathematics 88
John Doe English 97
Become
Name Chinese maths English
Sheet 395 90 89
Lee 492 88 97
*/
--The longitudinal table to horizontal table CREATE TABLE Biao (PName varchar (Ten), Pcourse varchar (Ten), Pgradeint) go insert INTO Biao values ('Zhang San','language',' the') insert into Biao values ('Zhang San','Mathematics',' -') insert into Biao values ('Zhang San','English',' the') insert into Biao values ('John Doe','language',' the') insert into Biao values ('John Doe','Mathematics',' the') insert into Biao values ('John Doe','English',' the') Select* fromBiao--Enter the conversion mode belowSelectPName, SUM ( CasePcourse when'language'Then PgradeElse 0End aslanguage, SUM ( CasePcourse when'Mathematics'Then PgradeElse 0End asMath, SUM ( CasePcourse when'English'Then PgradeElse 0End asEnglish fromBiao GROUP BY PName
-- horizontal table to longitudinal table --Example 2 creating table 2 CREATE TABLE BIAO1 (name varchar ( -), Languageint, Mathematicsint, Englishint) go insert INTO BIAO1 values ('Zhang San', the, -, the) insert into BIAO1 values ('John Doe', the, the, the) Select* fromBiao1--Enter the conversion mode belowSelect* fromBiao1SelectName'language' asCourses, Languages asResults fromBIAO1 Union AllSelectName'Mathematics' asCourses, Mathematics asResults fromBIAO1 Union AllSelectName'English' asCourses, English asResults frombiao1 ORDER BY name Desc
The horizontal table of the database and the longitudinal table to the horizontal table