Row and column conversion in the database

Zookeeper Problem Description

In the evaluation system, the row-to-column application of data is involved in the query of instructor scores. First, the following problems to be solved are described:

The Data Structure Stored in our database is similar:

In the end, we will achieve the following query results:

We divide the course column into three columns, and the data of the corresponding score column is inserted as a record to the end of the corresponding course.

Feature Source

Why is there such a demand?

During the database design process, to meet the user's Dynamic Requirements (adding fields), you can use a field name table to define a table with field values to achieve static expression of dynamics. That is to say, replacing the original horizontal extension with the vertical increase of the database table, that is, adding the replacement field for the number of records.

This design is flexible, but it also brings about the trouble of statistical analysis, because the statistical analysis may need to display the field expansion. For example, the specific implementation in the evaluation system:

As the assessment project will be dynamically added and deleted, the assessment project table is set to store the assessment items for instructors.

The table storing the instructor evaluation score needs to obtain the data of the Assessment project. The storage illustration is:

The final display result is that the evaluation project should be listed as the title, and the score should be added to the corresponding assessment project as a record.

Here, we store dynamic change assessment items as records of new tables to bring design flexibility, but during display, we need to convert the rows and columns to get the desired results.

Implementation

Find the relevant information for implementation, understand that the row-to-column conversion is divided into static and dynamic, static does not need to be extended, it is easy to implement, the function of the evaluation system is a dynamic implementation.

For the first instance

1. Dynamic row-to-column conversion through String concatenation

-- Assign values to variables in the order of SQL language: declare @ SQL varchar (500) set @ SQL = 'select name' select @ SQL = @ SQL + ', max (case course when''' + course + ''' then score else 0 end) ['+ course +'] 'from (select distinct course from tb) a -- same as from tb group by course, set @ SQL = @ SQL + 'from tb group by name' exec (@ SQL) by default)

2. Use isnull and functions

Declare @ SQL varchar (8000) -- Obtain the course set select @ SQL = isnull (@ SQL + ',','') + course from tb group by course set @ SQL = 'select * from tb limit (max (score) for course in ('+ @ SQL + ')) a' exec (@ SQL)

3. Requirements for adding the total score and average score

Declare @ SQL varchar (8000) select @ SQL = isnull (@ SQL + ',', '') + course from tb group by course set @ SQL = 'select m. *, n. total score, n. average from (select * from tb) a round (max (score) for course in ('+ @ SQL +') B) m, (select name, sum (score) total score, cast (avg (score * 1.0) as decimal () average score from tb group by name) nwhere m. name = n. name 'exec (@ SQL)

Summary

Although the evaluation system has implemented the conversion of row and column data, you want to use another method. After the implementation of the row-to-column search function, many problems have been encountered in the application transferred to the evaluation system, and it has not been completely solved yet, you have to put it first. We also have some experience in the flexible design of databases. While considering the flexibility, we also need to consider the user experience. We still need to think more about the transformation of rows and columns.

Link: http://www.2cto.com/database/201203/122708.html

Http://www.cnblogs.com/zhangzt/archive/2010/07/29/1787825.html