Pivot tables by SQL, database table row and column conversions (pivot and UNPIVOT usage) (i)

In MSSQL, we all know that pivot can be used to statistic data, and to implement the pivot table function like Excel.

First, our usual usage in the MSSQLServer

1. SQL Server database Test---Creating a test table create table S ([name] nvarchar (), book nvarchar (), salednumber int)----Inserting test data insert into S ([Name],book,salednumber] Values (' Xiao Wang ', ' Java from beginner to proficient '); INSERT into S ([Name],book,salednumber) VALUES (' Xiao Li ', ' Java from beginner to proficient ', "insert into S ([Name],book,salednumber] Values (' Xiao Wang ', ' C # Advanced programming ', 8); INSERT into S ([Name],book, Salednumber) VALUES (' Xiao Li ', ' Java from beginner to proficient ', 7); INSERT into S ([Name],book,salednumber) VALUES (' Xiao Wang ', ' Java from beginner to proficient ', 9); insert into S ([Name],book,salednumber] values (' Xiao Li ', ' Java from beginner to proficient ', 2); INSERT into S ([Name],book,salednumber) VALUES (' Xiao Wang ', ' C # Advanced programming ', 3); INSERT into S ([Name],book,salednumber] values (' Xiao Li ', ' Java from beginner to proficient ', 5); INSERT into S ([Name],book,salednumber)  Values (' Xiao Li ', ' C # Advanced programming ', 5);---1. Select [Name], sum (case book when ' Java from getting started to mastering ' then Salednumber else 0 end) as [Java from getting started to mastering],sum (case book when ' C # advanced programming ' then Salednumber else 0 end) as [C # advanced Programming], SUM (salednumber) as [sum] from S Grou P by [name]---2, through pivotselect Sa.name,sa.java from beginner to proficient, SA. C # Advanced Programming, Sa.java fromGet started to master +sa. C # Advanced programming as total from-s pivot (SUM (salednumber) for book in (Java from getting started to proficient, C # advanced programming)) SA

　　

II. Usage of Oracle

---Creating a test table create TABLE s (name varchar, book varchar (), Salednumber number (9))----Inserting test data insert into S (name,b Ook,salednumber) VALUES (' Xiao Wang ', ' Java from beginner to proficient ', insert into S (name,book,salednumber) values (' Xiao Li ', ' Java from beginner to proficient ', 15); Insert into S (name,book,salednumber) VALUES (' Xiao Wang ', ' C # Advanced programming ', 8); INSERT into S (name,book,salednumber) values (' Xiao Li ', ' Java from beginner to proficient ', 7); INSERT into S (name,book,salednumber) VALUES (' Xiao Wang ', ' Java from beginner to proficient ', 9); INSERT into S (name,book,salednumber Values (' Xiao Li ', ' Java from beginner to proficient ', 2); INSERT into S (name,book,salednumber) VALUES (' Xiao Wang ', ' C # Advanced Programming ', 3); INSERT into S (Name,book, Salednumber) VALUES (' Xiao Li ', ' Java from beginner to proficient ', 5); INSERT into S (name,book,salednumber) values (' Xiao Li ', ' C # Advanced programming ', 5);-------1, Select name, sum (case book when ' Java from getting started to mastering ' then Salednumber else 0 end) as Java from getting started to mastering, sum ' C # Advanced programming ' then Salednumber else 0 end) as C # advanced programming, SUM (salednumber) as sum from S group by name;---2, through pivotselect * FROM (s Elect Name,salednumber,book from S group by name, Salednumber, book) Pivot (SUM (salednumber) for book in (' Java from getting started to mastering ', ' C # Advanced programming '); 

　　

Describe the unclear or wrong place, I hope you corrected, for Oracle more about pivot and Unpivot

Make the crosstab report of a spreadsheet type with simple SQL display information from any relational table and store all the data in the crosstab into a relational table. Please refer to Oracle official example

Http://www.oracle.com/technetwork/cn/articles/11g-pivot-101924-zhs.html

Pivot tables by SQL, database table row and column conversions (pivot and UNPIVOT usage) (i)