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( -),--Student Name   [Subject]          NVARCHAR( -),--Subjects   [score]            FLOAT,--Achievements)INSERT  into [Studentscores] SELECT 'Zhang San','language', theINSERT  into [Studentscores] SELECT 'Zhang San','Mathematics', -INSERT  into [Studentscores] SELECT 'Zhang San','English', -INSERT  into [Studentscores] SELECT 'Zhang San','Biological', -INSERT  into [Studentscores] SELECT 'John Doe','language', theINSERT  into [Studentscores] SELECT 'John Doe','Mathematics', theINSERT  into [Studentscores] SELECT 'John Doe','English', theINSERT  into [Studentscores] SELECT 'John Doe','Biological', theINSERT  into [Studentscores] SELECT 'Yard Farm','language', -INSERT  into [Studentscores] SELECT 'Yard Farm','Mathematics', theINSERT  into [Studentscores] SELECT 'Yard Farm','English', theINSERT  into [Studentscores] SELECT 'Yard Farm','Biological', +

2. Row to column SQL

SELECT *  from [Studentscores] /*Data Source*/ asPpivot (SUM(Score/*value of column after row to column*/) forP.subject/*columns that require row to column*/ inch([language],[Mathematics],[English],[Biological]/*the value of the column*/))  asT

Execution Result:

Second, the list of career change

1. Test data Preparation

CREATE TABLEProgrectdetail (ProgrectnameNVARCHAR( -),--Project NameOverseasupplyINT,--supply of overseas suppliersNativesupplyINT,--Supply quantity of domestic supplierSouthsupplyINT,--South Supplier Supply QuantityNorthsupplyINT           --Supply quantity of north supplier)INSERT  intoProgrectdetailSELECT 'A', -, $, -, -UNION  AllSELECT 'B', $, -, Max, MaxUNION  AllSELECT 'C',159, -, -, theUNION  All

2. SQL for career change

SELECT P.progrectname,p.supplier,p.supplynum  from (    SELECT  progrectname, overseasupply, nativesupply,           southsupply, northsupply       from progrectdetail) tunpivot (        Overseasupply, Nativesupply, southsupply, northsupply) P

Execution Result:

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