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

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

go

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

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

/*2. pivot(聚合函數(要轉成列值的列名)
　      for 要轉換的列
 　　    in（目標列名）
 　　)*/

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

最新評論