/*創(chuàng)建數(shù)據(jù)庫(kù)*/
 CREATE DATABASE tmp
 go
 USE tmp
 go

/*創(chuàng)建數(shù)據(jù)庫(kù)測(cè)試表*/
CREATE TABLE [Scores]
     (
       [ID] INT IDENTITY(1, 1)
                PRIMARY KEY ,
       [Student] VARCHAR(20) ,
       [Subject] VARCHAR(30) ,
       [Score] FLOAT
     )

go

TRUNCATE TABLE Scores
 /*插入數(shù)據(jù)庫(kù)測(cè)試數(shù)據(jù)信息*/
  INSERT  INTO Scores
         ( Student, Subject, Score )
 VALUES  ( 'test001', '語(yǔ)文', '90' )
 INSERT  INTO Scores
         ( Student, Subject, Score )
 VALUES  ( 'test001', '英語(yǔ)', '85' )
 INSERT  INTO Scores
         ( Student, Subject, Score )
 VALUES  ( 'text002', '語(yǔ)文', '90' )
 INSERT  INTO Scores
         ( Student, Subject, Score )
 VALUES  ( 'text002', '英語(yǔ)', '80' )
 INSERT  INTO Scores
         ( Student, Subject, Score )
 VALUES  ( 'test003', '語(yǔ)文', '95' )
 INSERT  INTO Scores
         ( Student, Subject, Score )
 VALUES  ( 'test003', '英語(yǔ)', '85' )

/*1.  case when .......then else  ....end 用法，行列轉(zhuǎn)換*/
 SELECT  Student AS '姓名' ,
         MAX(CASE Subject
               WHEN '語(yǔ)文' THEN Score
               ELSE 0
             END) AS '語(yǔ)文' ,--如果這個(gè)行是“語(yǔ)文”，就選此行作為列
        MAX(CASE Subject
               WHEN '英語(yǔ)' THEN Score
               ELSE 0
             END) AS '英語(yǔ)'
FROM    Scores
 GROUP BY Student
 ORDER BY Student

/*2. pivot(聚合函數(shù)(要轉(zhuǎn)成列值的列名)
　      for 要轉(zhuǎn)換的列
 　　    in（目標(biāo)列名）
 　　)*/

SELECT  Student AS '姓名' ,
         AVG(語(yǔ)文) AS '語(yǔ)文' ,
         AVG(英語(yǔ)) AS '英語(yǔ)'
FROM    Scores PIVOT( AVG(Score) FOR Subject IN ( 語(yǔ)文, 英語(yǔ) ) )as NewScores
 GROUP BY Student
 ORDER BY Student ASC

最新評(píng)論