测试数据:
create table StudentScore
(
Name varchar(12) NULL,
ClassName varchar(12) NULL,
Grade int NULL
)
INSERT INTO StudentScore(Name,ClassName,Grade)
VALUES
('张三','语文','78'),('张三','数学','79'),('张三','英语','80'),
('张三','物理','81'),('张三','政治','82'),
('李四','语文','84'),('李四','数学','85'),('李四','英语','86'),
('李四','物理','83'),('李四','政治','87'),
('王五','语文','89'),('王五','数学','82'),('王五','英语','90'),
('王五','物理','81'),('王五','政治','91')
创建完测试数据后,SELECT后的结果如图:
要求写一条SQL查询语句,查询结果如下列显示
姓名 语文 数学 英语 物理 政治
张三 78 79 80 81 82
李四 83 84 85 86 87
王五 88 89 90 91 92
1 利用CASE WHEN关键字
SELECT Name,MAX(CASE ClassName WHEN '语文' THEN Grade ELSE 0 END)语文,
MAX(CASE ClassName WHEN '数学' THEN Grade ELSE 0 END)数学,
MAX(CASE ClassName WHEN '英语' THEN Grade ELSE 0 END)英语,
MAX(CASE ClassName WHEN '物理' THEN Grade ELSE 0 END)物理,
MAX(CASE ClassName WHEN '政治' THEN Grade ELSE 0 END)政治
FROM StudentScore GROUP BY Name
效果图:
2 利用PIVOT运算符
SELECT Name,[语文],[数学],[英语],[物理],[政治]
FROM StudentScore
PIVOT
(
MAX(Grade)
FOR ClassName IN ([语文],[数学],[英语],[物理],[政治])
) AS PivotTable
PIVOT是是sql server 2005新特性。
SELECT <non-pivoted column>,
--不要聚合的列
[first pivoted column] AS <column name>,
[second pivoted column] AS <column name>,
...
[last pivoted column] AS <column name>
FROM
(<SELECT query that produces the data>)
AS <alias for the source query>
PIVOT
(
<aggregation function>(<column being aggregated>)
--聚合函数加将要转置成行的列的数据
FOR
[<column that contains the values that will become column headers>] --要被转置成行的列
IN ( [first pivoted column], [second pivoted column],
... [last pivoted column])
) AS <alias for the pivot table>
<optional ORDER BY clause>;