SQL Server generates a pivot table

A pivot table is a method for analyzing data. It contains a powerful data pivot function in Excel. What is data pivoting? It may be easier to give an example. Assume there is a data table:

Sales volume of books

----------------------------------------

Wang Excel teaching material 10

Xiao Li Excel teaching material 15

Wang Word teaching material 8

Xiao Li Excel teaching material 7

John Excel teaching material 9

Xiao Li Excel teaching material 2

Wang Word textbook 3

Xiao Li Excel textbook 5

One method of data pivoting is to count the sales volume of each type of books by each salesperson. The results are as follows:

----------------------------------------------------------------

Excel teaching materials, Word teaching materials, total

---------------------------------------------------------------

John 29 0 29

Xiao Li 19 11 30

Do you understand? This is the simplest type of data pivoting, and you can have multi-level groups if necessary.

Well, how can we see the data pivoting function in SQL Server? I am a beginner of SQL Server. I read some examples on the Internet and wrote the following SQL statements based on my own understanding.

Code for generating basic data

Create table s ( [Name] nvarchar (50 ), Book nvarchar (50 ), SaledNumber int ) Insert into s ([name], book, saledNumber) values ('mr. Wang ', 'excel textbooks', 10 ); Insert into s ([name], book, saledNumber) values ('lil', 'excel textbooks ', 15 ); Insert into s ([name], book, saledNumber) values ('wang ', 'word textbooks', 8 ); Insert into s ([name], book, saledNumber) values ('lil', 'excel textbooks ', 7 ); Insert into s ([name], book, saledNumber) values ('mr. Wang ', 'excel textbooks', 9 ); Insert into s ([name], book, saledNumber) values ('lil', 'excel textbooks ', 2 ); Insert into s ([name], book, saledNumber) values ('mr. Wang ', 'word textbook', 3 ); Insert into s ([name], book, saledNumber) values ('lil', 'excel textbooks ', 5 );

Generate a PivotTable

Set @ SQL = 'select [name],' Select @ SQL = @ SQL + 'sum (case book when' + quotename (book, ''') + 'then saledNumber else 0 end) as '+ quotename (book) + ',' from s group by book Select @ SQL = left (@ SQL, len (@ SQL)-1) Select @ SQL = @ SQL + ', sum (saledNumber) as [sum] from s group by [name]' Select @ SQL Exec (@ SQL)

The preceding query statement first concatenates an "SQL statement" and the final result is:

SELECT [name], sum (case book when 'excel teaching material 'then saledNumber else 0 end) as [Excel teaching material], sum (case book when 'word textbook 'then saledNumber else 0 end) as [Word textbook], sum (saledNumber) as [sum] from s group by [name]

Of course, if the data in the table is different, the generated SQL statement is also different. Finally, it calls the SQL Server System Stored Procedure Exec to execute this statement. Cut a chart.

This is the implementation of generating a pivot table in SQL Server. In fact, its core is the SQL statement spliced above. More complex pivoting methods, such as multi-level pivoting, are also implemented on this basis.