数据库行列转换（示例mySQL 5.1）

原表：

表名 :user

name          course    grade

zhangsan      java        70

zhangsan      C++         80

lisi             java      90

lisi             C#        60

用一条 SQL 语句得到如下形式：

name     java   C++   C#

zhangsan  70     80   null

lisi      90    null  60

 

 

分析：这是典型的行列转换问题，现给出两种转换语句：

SQL1：

 

select   name,
sum(case when course='java' then grade end) as java,
sum(case when course='C++' then grade end) as C++,
sum(case when course='C#' then grade end) as C#
from test group by name

 

SQL2：

select  distinct c.`name` AS name,
(select grade from test where name = c.`name` and course = 'java' )as java,
(select grade from test where name = c.`name` and course = 'C++' )as C++,
(select grade from test where name = c.`name` and course = 'C#' )as C#
from test c