TIPS: use SQL Server to generate 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 teaching material '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.

Original article title: SQL Server generates a pivot table

Link: http://www.cnblogs.com/shangfc/archive/2010/09/14/1826307.html

Edit recommendations]