Oracle 行转列方法

在应用系统开发中我们经常会遇到行转列问题，尤其对于数据分析、报表系统中。本文将介绍在Oracle数据库中对数值、文本字段如何进行行转列。

首先我们创建一个测试表，表脚本为如下所示：

--创建表

create table item_score    

(

    code            varchar2(32)  not null,   --考号

    itemid          number(10),     --题号

    score           number(10,2),  --成绩

    score_level  varchar2(1)     --成绩等级A,B

);

 

--插入测试数据

declare
vscore number;
begin
  vscore:=dbms_random.value(0,10);
  insert into item_score(code, itemid, score, score_level)
  values('10001', 1,vscore,decode(sign(vscore-5),-1,'B','A'));
 
  vscore:=dbms_random.value(0,10);
  insert into item_score(code, itemid, score, score_level)
  values('10001', 2,vscore,decode(sign(vscore-5),-1,'B','A'));
 
  vscore:=dbms_random.value(0,10);
  insert into item_score(code, itemid, score, score_level)
  values('10002', 1,vscore,decode(sign(vscore-5),-1,'B','A'));
 
  vscore:=dbms_random.value(0,10);
  insert into item_score(code, itemid, score, score_level)
  values('10002', 2,vscore,decode(sign(vscore-5),-1,'B','A'));
  commit;
end;

 

--查询数据

select * from item_score

 

得到结果：

 

    CODE ITEMID SCORE SCORE_LEVEL
   10001 1          2.90     B

   10001 2          1.59     B
   10002 1          8.77     A
   10002 2          2.59     B

 

表item_score中将学生各题成绩横向存储的，但是在实际中往往需要看到如下成绩单：

 

考号   ,题目1, 等级1,题目2, ....

1001 ,得分 , 等级  ,得分, ...

1002 ,得分 , 等级  ,得分, ...

 

为得到以上查询结果我们需要进行行转列，就是需要生成一个交叉表.

 

1.行转列(itemid)的值字段(score)是数值型字段进行行转列.我们通常配合使用sum,decode来完成，但必须保证要进行转换的列的值对于分组列是唯一的.

select code --分组列
       ,sum(decode(itemid,1,score,0)) item_1 --题1
       ,sum(decode(itemid,2,score,0)) item_2 --题2
from item_score group by code order by code

执行以上语句我们就可以得到以下结果：

    CODE    ITEM_1    ITEM_2

   --------------------------------
   10002    8.77         2.59
   10001    2.9           1.59
2.行转列(itemid)的值字段(score_level)是文本型字段进行行转列.我们通常使用wmsys.wm_concat,decode函数配合使用.

select code   --分组列     
       ,wmsys.wm_concat(decode(itemid,1,score_level,'')) level_1
       ,wmsys.wm_concat(decode(itemid,2,score_level,'')) level_2
from item_score group by code order by code

执行以上语句我们就可以得到以下结果：

    CODE      LEVEL_1      LEVEL_2
   10001      B                 B
   10002      A                 B

合并1，2中的语句就可以得到我们想要的结果：

select code "考号"    
       ,sum(decode(itemid,1,score,0)) "第1题"
       ,wmsys.wm_concat(decode(itemid,1,score_level,'')) "第1题等级"
       ,sum(decode(itemid,2,score,0)) "第2题"
       ,wmsys.wm_concat(decode(itemid,2,score_level,'')) "第2题等级"
from item_score group by code order by code

执行以上语句我们就可以得到以下结果：

    考号     第1题     第1题等级      第2题      第2题等级
10001     2.9        B                 1.59        B
10002     8.77      A                 2.59        B

用以上两种方法基本可以解决我们常见的交叉表问题.当然很多报表组件(Crystal Reports,microsoft reports)等都提供交叉表功能，如果使用这些组件的话可以不用在数据库层面解决交叉表问题.