How to Convert Horizontal and Vertical Tables in SQL

catalogue1 Overview of Horizontal Tables2 Overview of the vertical table3 The code for converting a vertical table to a horizontal table is as follows and the view is displayed4 The code for converting horizontal table to vertical table is as follows and the view is displayed5 Advantages and disadvantages of horizontal and vertical tables6 Summary1 Overview of Horizontal TablesHorizontal table is a common way to create tables. For example, the table structure is primary key, field 1, field 2, and field 3…

1 Overview of Horizontal Tables

Horizontal table is a common way to create tables. For example, the table structure is primary key, field 1, field 2, and field 3….

2 Overview of the vertical table

The table structure of a vertical table consists of primary keys, field codes, and field values, while the field codes are Field 1, Field 2, and Field 3….

3 The code for converting a vertical table to a horizontal table is as follows and the view is displayed

1. The vertical view is as follows:

2. Convert the vertical table to a horizontal table view as follows:

3. The SQL statement is as follows:

selectstudent_ Name, sum (cases. subjectwhen 'C language' themes. scorelse 'end) as C language, sum (cases. subjectwhen' data structure 'themes. scorelse' end) as data structure, sum (cases. subjectwhen 'operating system' themes. scorelse 'end) as operating system from t_ studenttsgroupbyts.student_ name;

4 The code for converting horizontal table to vertical table is as follows and the view is displayed

1. The horizontal view is as follows:

2. Convert the horizontal table to a vertical table view as follows:

3. The SQL statement is as follows:

selectts.studnet_ Name, 'C language' as subject, ts` C Language ` as Score from t_ student1tsunionallselectts.studnet_ Name, 'Data Structure' as Account, ts` Data Structure ` as Score from t_ student1tsunionallselectts.studnet_ Name, 'Operating System' as account, ts` Operating System ` as Score from t_ student1tsorderbystudnet_ Name, subject

5 Advantages and disadvantages of horizontal and vertical tables

1. Horizontal Table
Advantages: One line represents an entity record, which is clearly visible.
Disadvantage: If you want to add a field to this table now, you must rebuild the table structure.

2. Vertical Table
Advantages: If you want to add a field to this table now, you only need to add some records.
Disadvantage: The data description is not very clear, and it can cause a lot of database data. If you need to group statistics, you need to group by first, which is more cumbersome

6 Summary

We should design tables that are not easy to change as horizontal tables and tables that are prone to frequent and uncertain changes as vertical tables.

That's all for this article on how SQL implements the conversion between horizontal and vertical tables. For more related content on SQL horizontal and vertical table conversion, please search for previous articles or continue browsing related articles below. We hope everyone can support Script Home more in the future!