How to use SQL statements to convert rows and columns

How to use SQL statements to convert rows and columns

Row-to-column conversion is a common requirement in the database system. During database design, in order to fit the accumulated storage of data, direct record is often used, while displaying data, you want to organize all records and display them in a transpose manner. Figure 9.1 shows the row/column conversion function.

Figure 9.1 requirements for column-and-column Conversion

By analyzing this requirement, we can find records with the same department and accumulate the quantity based on the value of the materials. If you write it manually, the following SQL statement will be obtained:

Select department, Sum (case material when 'material 1' then quantity else 0 end) [material 1], Sum (case material when' material 2' then quantity else 0 end) [material 2], Sum (case material when 'material 3' Number of then else 0 end) [material 3] From Department Consumables Group by department

This is a very simple query statement, and the execution result is exactly the expected result. But the question is, how can we know that the original table actually contains several materials? Obviously, according to the preceding SQL statement, the results can only calculate the consumption of three kinds of materials. In this case, you need to dynamically obtain the query statement based on the actual number of materials. Code 9-1 implements a dynamic row/column conversion.

Code 9-1 dynamic row/column conversion: transfer. SQL

 

-- Declare a string variable for dynamic assembly Declare @ SQL varchar (8000) -- Assemble SQL commands Set @ SQL = 'select Department' -- Obtain materials dynamically and create a column for each material Select @ SQL = @ SQL + ', sum (Case item when ''' + item + ''' Then number else 0 end) ['+ item +']' From (select distinct item from distinct cost) as -- The selection source and group by statements are added. Select @ SQL = @ SQL + 'from your cost group by Department' -- Execute SQL commands Exec (@ SQL)

 

For ease of writing, neither table nor column names use Chinese names. We recommend that you avoid using Chinese characters directly when designing a database. You can use Pinyin or abbreviations instead.

The execution result of this SQL command is as follows:

Department	Item1	Item2	Item3
F1	3	1	2
F2	0	2	1
F3	1	0	1

Such a solution still has many defects. There are two main points: first, the efficiency of dynamic SQL command execution is often not high, because of dynamic assembly, the database management system cannot optimize such commands; second, such an SQL command must first determine its length limit, while the length of a dynamic SQL command is often changed according to the actual table content, so this command cannot be run by 100%. AnswerSQL commands for Row-to-column conversion usually rely on dynamic SQL statements. For specific implementation methods, see the problem analysis in this section.