关于竖表转横表的问题

关于竖表转横表的问题

                                                                                本文作者：dinya

内容摘要：在开发过程，经常遇到一些将表的显示方式进行转换的需求，我们习惯性称之为竖表到横表的转换，本文通过一个例子来简要说明常见的两种竖表转横表的问题。

本文适宜读者范围：Oracle初级，中级

系统环境：

　　    OS：windows 2000 Professional (英文版)

Oracle：8.1.7.1.0

正文：

在实际的应用中，我们经常遇到需要转换数据显示方式，比如将横表转为竖表，或将竖表转换为横表的情况，如：课程表的显示方式，部门平均工资的排名等情况。下面将将根据两个实例子的需求描述给出两种常见的竖表转横表的解决办法（本例中的数据意思是：一、二、三年级的各科目最高分统计）。

表结构：

create table test_table
(
    grade_id number(8),             --年级：１、一年级，２、二年级，３、三年级
    subject_name varchar2(30),    --科目：包含语文、数学、外语、政治等科目
    max_score number(8)             --最高分
)

表中数据：

SQL> select * from test_table;

 GRADE_ID    SUBJECT_NAME                   MAX_SCORE

        1      语文                                  95

        1      数学                                  98

        2      语文                                  86

        2      数学                                  90

        2      政治                                  87

        3      语文                                  93

        3      数学                                  88

        3      英语                                  88

        3      政治                                  97

9 rows selected.

第一种转换方式：

需求描述：查看每个年级在系统中存在的科目信息，并各年级的科目信息按下面的格式显示：

GRADE_ID      SUBJECT_NAME  

         1      语文  数学

　　　　2      语文  数学  政治

　　　　 3      语文  数学  英语  政治

分析：在要求得到的结果中，每个年级的科目将变成一条记录，而且每个年级的科目是不固定的。所以考虑写个函数来解决，输入年级信息，使用游标得到该年级的所有科目信息并返回值。

1、建函数：

SQL> create or replace function test_fun(p_grade number) return varchar2 as

  2      v_temp varchar2(100):='';

  3      v_out varchar2(500):='';

  4      cursor c is select a.subject_name from test_table a where a.grade_id=p_grade;

  5  begin

  6      open c ;　　　--打开游标

  7      loop

  8          fetch c into v_temp;

  9          exit when c%notfound;

 10          v_out:=v_out||'  '||v_temp;

 11      end loop;

 12      close c;      --关闭游标

 13      return v_out;

 14      exception

 15          when others then

 16              return 'An error occured';       

 17  end ;

 18  /

Function created.

SQL> create or replace function test_fun(p_grade number) return varchar2 as

  2      v_out varchar2(500):='';

  3      cursor c is select a.subject_name from test_table a where a.grade_id=p_grade;

  4  begin

  5      for v_temp in c loop

  6          v_out:=v_out||'  '||v_temp.subject_name;

  7      end loop;       --系统自动关闭游标

  8      return v_out;

  9      exception

 10          when others then

 11              return 'An error occured';

 12  end ;

 13  /

Function created.

2、调用函数得到输入结果：

SQL> select distinct a.grade_id,test_fun(a.grade_id) subject from test_table a；

 GRADE_ID            SUBJECT

1        语文  数学

2         语文  数学  政治

3         语文  数学  英语  政治

第二种转换方式：

需求描述：要求将表中的年级、科目及最高的信息按照下表的格式显示,如果该年级没开的课程,则其最高分用0表示:

年级       语文          数学         英语　　　　　　政治

一年级        95           98            0               0

二年级        86           90            0              87

三年级        93           88           88              97

分析：该需求将年级的分数及科目信息由纵向转为横向，这样就要针对每个年级的，对其科目进行判断，存在科目则显示科目的最高分，如果不存在显示0。这时候就考虑到使用decode函数来解决。实现如下：

 select
    decode(t.grade_id,1,'一年级',2,'二年级',3,'三年级') 年级,
    sum(decode(t.subject_name,’语文’,t.max_score,0)) 语文,
    sum(decode(t.subject_name,'数学',t.max_score,0)) 数学,
    sum(decode(t.subject_name,'英语',t.max_score,0)) 英语,
    sum(decode(t.subject_name,'政治',t.max_score,0)) 政治
from
    test_table t
group by
    t.grade_id

需要说明的是，在第一种转换方式中写了两个函数,两个函数实现的是同一个需求，所不同的是，两个函数中游标使用方式不同，地一个函数中手动打开游标，循环结束后要求手动关闭。而后一个函数使用for 循环，循环结束后系统自动关闭光标。在第二种转换方式中，使用了decode函数，关于decode的详细用法，请参考oracle函数相关文档。

总　　结：

上面的两种转换方式是在开发中经常遇到的情况,在开发中的其他类似的转换都可以参考上面的转换方式,使用decode,nvl等函数进行一些特别的处理即可得到想要的显示方式.