Use SQL to perform pivot tables, and convert rows and columns of database tables (usage of tables and unstatements) (1 ).

Use SQL to perform pivot tables, and convert rows and columns of database tables (usage of tables and unstatements) (1 ).

As you all know in mssql, you can use the pivot to collect statistics and implement the pivot table function like excel.

I. Common usage in MSsqlserver

1. SQL Server database test --- Create table s ([name] nvarchar (50), book nvarchar (50), saledNumber int) ---- insert test data insert into s ([name], book, saledNumber) values ('John ', 'java from entry to Master', 10 ); insert into s ([name], book, saledNumber) values ('lil', 'java from entry to Master', 15); insert into s ([name], book, saledNumber) values ('wang ', 'c # advanced programming', 8); insert into s ([name], book, saledNumber) values ('lily ', 'java from entry to Master', 7); insert into s ([name], book, saledNumber) values ('John ', 'java from entry to Master', 9 ); insert into s ([name], book, saledNumber) values ('lil', 'java from entry to Master', 2); insert into s ([name], book, saledNumber) values ('wang ', 'c # advanced programming', 3); insert into s ([name], book, saledNumber) values ('lily ', 'java from entry to Master', 5); insert into s ([name], book, saledNumber) values ('lil', 'c # advanced programming ', 5 ); --- 1. SELECT [name] In case when mode, sum (case book when 'java from getting started to mastering 'then saledNumber else 0 end) as [java from getting started to proficient], sum (case book when 'C # advanced programming 'then saledNumber else 0 end) as [C # advanced programming], sum (saledNumber) as [sum] from s group by [name] --- 2. Use Limit tselect sa. name, sa. java from entry to mastery, sa. C # advanced programming, sa. java from entry to proficiency + sa. C # advanced programming as from s aggregation (sum (saledNumber) for book in (java from entry to proficiency, C # advanced programming) sa

　　

Ii. oracle usage

--- Create test table Create table s (name varchar (50), book varchar (50), saledNumber number (9) ---- insert test data into s (name, book, saledNumber) values ('little king', 'java from entry to Master', 10); insert into s (name, book, saledNumber) values ('lily ', 'java from entry to Master', 15); insert into s (name, book, saledNumber) values ('John ', 'c # advanced programming', 8 ); insert into s (name, book, saledNumber) values ('lil', 'java from entry to Master', 7); insert into s (name, book, saledNumber) values ('little king', 'java from entry to Master', 9); insert into s (name, book, saledNumber) values ('lily ', 'java from entry to Master', 2); insert into s (name, book, saledNumber) values ('John ', 'c # advanced programming', 3 ); insert into s (name, book, saledNumber) values ('lil', 'java from entry to Master', 5); insert into s (name, book, saledNumber) values ('lil', 'c # advanced programming ', 5); ------- 1. SELECT name through case when, sum (case book when 'java from entry to master' then saledNumber else 0 end) as java from entry to mastery, sum (case book when 'C # advanced programming 'then saledNumber else 0 end) as C # advanced programming, sum (saledNumber) as sum from s group by name; --- 2. Use fig (select name, saledNumber, book from s group by name, saledNumber, book) aggregate (sum (saledNumber) for book in ('java from entry to Master', 'c # advanced programming '));

　　

If the description is unclear or there is something wrong, we hope you can correct it.