Row-to-column (PIVOT) and column-changing for SQL (UNPIVOT)

When it comes to statistics, it is often a problem to change rows and columns. Case when mode is too cumbersome, and scalability is not strong, you can use Pivot,unpivot to quickly achieve row to column, column change, and Scalability strong

First, row to column

1. Test data Preparation

CREATE  TABLE [studentscores] (   [UserName]         NVARCHAR (a),        --Student name   [Subject]          NVARCHAR (30),        --Subjects   [Score]            FLOAT,               --score) INSERT into [studentscores] select ' Zhang San ', ' language ', 80INSERT into [studentscores] select ' Zhang San ', ' math ', 90INSERT I NTO [studentscores] select ' Zhang San ', ' English ', 70INSERT into [studentscores] select ' Zhang San ', ' creature ', 85INSERT into [studentscores] Sele CT ' John Doe ', ' language ', 80INSERT into [studentscores] select ' John Doe ', ' math ', 92INSERT into [studentscores] select ' John Doe ', ' English ', 76INSERT into [studentscores] select ' John Doe ', ' creature ', 88INSERT into [studentscores] select ' yards ', ' languages ', 60INSERT into [studentscores] SEL ECT ' Code farmer ', ' math ', 82INSERT into [studentscores] select ' yards ', ' English ', 96INSERT into [studentscores] select ' yards ', ' creature ', 78

2. Row to column SQL

SELECT * FROM [studentscores]/* Data source */as ppivot (    SUM (the value of column after score/* row to column */)     for p.subject/* the column that requires row to column */in ([Language],[math],[ English],[Biological]/* column value */)) as T

Execution Result:

Second, the list of career change

1. Test data Preparation

CREATE TABLE progrectdetail (    progrectname         NVARCHAR (20),--project name    overseasupply        INT,          --supply quantity of overseas suppliers    nativesupply         int,          --Domestic supplier supply quantity    southsupply          int-          -South supplier Supply Quantity    northsupply          int           -North supplier supply quantity) INSERT into Progrectdetailselect ' A ', 50UNION allselect ' B ', 150UNION allselect ' C ', 159, 400 , 320UNION All

2. SQL for career change

Select P.progrectname,p.supplier,p.supplynumfrom (    select Progrectname, overseasupply, nativesupply,           Southsupply, northsupply from     progrectdetail) tunpivot (supplynum    -Supplier    in (overseasupply, Nativesupply, Southsupply, northsupply) P

Execution Result:

Row-to-column (PIVOT) and column-changing for SQL (UNPIVOT)