数据库行列转换

以ORACLE举例说明：

 

可以使用多种方法实现，如：自连接、case when、decode。

 

比如有表test026：

        ID NAME                 SUBJECT                   SCORE
---------- -------------------- -------------------- ----------
         1 jim                  语文                         88
         1 jim                  数学                         84
         1 jim                  英语                         90
         2 kate                 语文                         86
         2 kate                 数学                         76
         2 kate                 英语                         96

想得到如下效果：

学生编号 学生姓名   语文 数学 英语

 

方法：

1.自连接：(这是自连接很典型的用处 应当熟练掌握)

select a.id,a.name,a.score as "语文",b.score as "数学",c.score as "英语"
2   from test026 a,test026 b,test026 c
3   where a.id=b.id and a.subject='语文' and b.subject='数学'
4   and a.id=c.id and c.subject='英语';

        ID NAME                       语文       数学       英语
---------- -------------------- ---------- ---------- ----------
         1 jim                            88         84         90
         2 kate                         86         76         96

2 使用case when

sql@kokooa>select id,name,
2 sum(case when subject='语文' then score end) as "语文",
3 sum(case when subject='数学' then score end) as "数学",
4 sum(case when subject='英语' then score end) as "英语"
5   from test026
6 group by id,name
7 /

        ID NAME                       语文       数学       英语
---------- -------------------- ---------- ---------- ----------
         1 jim                           88         84         90
         2 kate                         86         76         96

3 decode

1 select max(id) as id,name,
2 max(decode(subject,'数学',score)) as "数学",
3 max(decode(subject,'语文',score)) as "语文",
4 max(decode(subject,'英语',score)) as "英语"
5 from test026
6* group by name

        ID NAME                       数学       语文       英语
---------- -------------------- ---------- ---------- ----------
         1 jim                          84         88         90
         2 kate                         76         86         96

 

原文：http://hi.baidu.com/loveyurui/blog/item/75a66a61c5ad396a0d33fa21.html