SQL Server generates a PivotTable report

A PivotTable report is a way to analyze your data, and it contains powerful pivot features in Excel. What does the data perspective look like? Giving an example may be easier to understand. Suppose you have a data table:

Sales people book sales

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

Xiao Wang Excel textbook 10

Xiao Li Excel Textbook 15

Xiao Wang Word textbook 8

Xiao Li Excel Textbook 7

Xiao Wang Excel textbook 9

Xiao Li Excel textbook 2

Xiao Wang Word textbook 3

Xiao Li Excel Textbook 5

One way to do this is to count each salesperson's sales for each type of book, with the following results

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

Excel textbook Word Textbook totals

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

Xiao Wang 29 0 29

Xiao Li 19 11 30

Do you understand? This is the simplest kind of data perspective, and you can have multi-level groupings if necessary.

Okay, so how does that look in SQL Server? I am a beginner of SQL Server, looking at some examples on the web, and combining my own understanding to write the following SQL statements.

Code that generates the underlying data

Create Table S ( [name] nvarchar (50), Book nvarchar (50), Salednumber int ) insert into S ([Name],book,salednumber) VALUES (' Xiao Wang ', ' Excel textbook ', 10); insert into S ([Name],book,salednumber) VALUES (' Xiao Li ', ' Excel textbook ', 15); insert into S ([Name],book,salednumber) VALUES (' Xiao Wang ', ' word textbook ', 8); insert into S ([Name],book,salednumber) VALUES (' Xiao Li ', ' Excel textbook ', 7); insert into S ([Name],book,salednumber) VALUES (' Xiao Wang ', ' Excel textbook ', 9); insert into S ([Name],book,salednumber) VALUES (' Xiao Li ', ' Excel textbook ', 2); insert into S ([Name],book,salednumber) VALUES (' Xiao Wang ', ' word textbook ', 3); insert into S ([Name],book,salednumber) VALUES (' Xiao Li ', ' Excel textbook ', 5);

To generate a PivotTable report

Set @sql = ' SELECT [name], ' Select @sql = @sql + ' sum (book, ' +quotename ") + ' 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 query above begins with a concatenation of an "SQL statement", and its final result is:

SELECT [name], sum (Case books when ' Excel textbook ' then salednumber else 0 end) as [Excel textbook],sum (case book when ' word textbook ' then Sal Ednumber 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 resulting SQL statement is different. Finally, it invokes SQL Server's system stored procedure exec to execute the statement. Cut a picture.

This is the implementation of generating a PivotTable report in SQL Server, but its core is the same SQL statement that was stitched up above. More complex perspective methods, such as multi-level perspective, are also implemented on this basis.