TIPS: use SQL Server to generate a pivot table

Source: Internet
Author: User

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

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

Generate a PivotTable

 
 
  1. set @sql = 'SELECT [name], ' 
  2. select @sql = @sql + 'sum(case  book when '+quotename(book,'''')+' then saledNumber else 0 end) as ' + quotename(book)+','  
  3. from s group by book  
  4. select @sql = left(@sql,len(@sql)-1)  
  5. select @sql = @sql + ', sum(saledNumber) as [sum] from s group by [name]' 
  6. select @sql  
  7. exec(@sql) 

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

 
 
  1. SELECT [name], sum (case book when 'excel teaching material 'then saledNumber else 0 end)
  2. As [Excel teaching material], sum (case book when 'word teaching material 'then saledNumber else 0 end)
  3. 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]

Related Article

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.