SQL Horizontal table conversion and comparison

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