Horizontal Table to longitudinal table
Horizontal table Style:
SQL statements:
1 SelectName,'language' asSubjects, Chinese asResults fromHorizontaltab2 Union All3 SelectName,'Mathematics' asSubjects, Math asResults fromHorizontaltab4 Union All5 SelectName,'English' asSubjects, 中文版 asResults fromHorizontaltab6Order by Name, account desc
Conversion Result:
Advantages and disadvantages of horizontal table:
Advantage: One line is an entity.
Cons: Add a field and need to re-build the table.
Conclusion: A table with small changes can use a horizontal table.
-----------------------------------------------------------
longitudinal table to Horizontal table
Chart Style:
SQL statements:
1 SelectName,sum ( CaseWhen Course=n'language'Then gradeElse 0End as 'language',2SUM ( CaseWhen Course=n'Mathematics'Then gradeElse 0End as 'Mathematics',3SUM ( CaseWhen Course=n'English'Then gradeElse 0End as 'English'4 fromVerticaltab GROUP BY Name
Conversion Result:
Advantages and disadvantages of the longitudinal table:
Advantages: Easy to add fields.
Cons: The relationship between data is not clear, there will be a surge in data volume situation.
Conclusion: It is possible to consider the longitudinal table for the table with frequent alteration of uncertainties.
SQL Horizontal table conversion and comparison