---row to column--pivot
CREATE TABLE TempTable
(
ID int primary key identity (.),
Student nvarchar (36),
[Subject] nvarchar (36),
Score int,
)
SELECT * FROM TempTable
INSERT into temptable values (' Zhang San ', ' language ', ' 90 ')
INSERT into temptable values (' Zhang San ', ' language ', ' 89 ')
INSERT into temptable values (' John Doe ', ' language ', ' 90 ')
INSERT into temptable values (' Harry ', ' language ', ' 93 ')
INSERT into temptable values (' Zhang San ', ' math ', ' 89 ')
INSERT into temptable values (' John Doe ', ' math ', ' 79 ')
INSERT into temptable values (' Harry ', ' Math ', ' 88 ')
INSERT into temptable values (' Zhang San ', ' English ', ' 87 ')
INSERT into temptable values (' John Doe ', ' English ', ' 94 ')
INSERT into temptable values (' Harry ', ' English ', ' 96 ')
Select Student,sum (language) as language, sum (mathematics) as Mathematics, SUM (English) as English to Tempsubject from TempTable Pivot (AVG (score) for [Subject] in (Chinese, Maths, English)) As A GROUP by Student ORDER BY language desc
---row career unpivot
SELECT * FROM Tempsubject
Unpivot
(
Score for [Subject] in (Chinese, maths, English)
)
As F
This article is from the "stubborn 蝸 cow" blog, please be sure to keep this source http://5090844.blog.51cto.com/5080844/1615243
SQL row and column interchange is very convenient