Oracle中实现行列转换的方法

转载 2007年09月26日 11:04:00

们在写SQL语句的时候经常需要用到行与列的转换问题,对于一个新手来说可能比较困难,其实你只要能够熟练运用Decode和Sum函数,这个问题就迎刃而解.

 


 


Create   table   test   (name   char(10),km   char(10),cj   Number)

insert   into   test   values('张三','语文',80)
insert   into   test   values('张三','数学',86)
insert   into   test   values('张三','英语',75)
insert   into   test   values('李四','语文',78)
insert   into   test   values('李四','数学',85)
insert   into   test   values('李四','英语',78)

commit;

 



怎样实现成这样:
name     语文     数学     英语
李四     78         85         83
张三     80         86         75

 

16:03:50   SQL>   select   *   from   test;

NAME               KM                                   CJ
----------   ----------   ----------
张三               语文                               80
张三               数学                               86
张三               英语                               75
李四               语文                               78
李四               数学                               85
李四               英语                               78

已选择6行。

已用时间:     00:   00:   00.47

 



16:03:55   SQL>   select   name,sum(decode(trim(km),'语文',cj,0))   语文,
16:03:58       2     sum(decode(trim(km),'数学',cj,0))   数学,
16:03:58       3     sum(decode(trim(km),'英语',cj,0))   英语
16:03:59       4     from   test   group   by   name;

NAME                           语文               数学               英语
----------   ----------   ----------   ----------
李四                               78                   85                   78
张三                               80                   86                   75



已用时间:     00:   00:   00.15
16:03:59   SQL>
[Q]如何实现行列转换
[A]1、固定列数的行列转换

student subject grade
---------------------------
student1 语文 80
student1 数学 70
student1 英语 60
student2 语文 90
student2 数学 80
student2 英语 100
……
转换为
语文 数学 英语
student1 80 70 60
student2 90 80 100
……
语句如下:
select student,sum(decode(subject,'语文', grade,null)) "语文",
sum(decode(subject,'数学', grade,null)) "数学",
sum(decode(subject,'英语', grade,null)) "英语"
from table
group by student

2、不定列行列转换

c1 c2
--------------
1 我
1 是
1 谁
2 知
2 道
3 不
……
转换为
1 我是谁
2 知道
3 不
这一类型的转换必须借助于PL/SQL来完成,这里给一个例子
CREATE OR REPLACE FUNCTION get_c2(tmp_c1 NUMBER)
RETURN VARCHAR2
IS
Col_c2 VARCHAR2(4000);
BEGIN
FOR cur IN (SELECT c2 FROM t WHERE c1=tmp_c1) LOOP
Col_c2 := Col_c2||cur.c2;
END LOOP;
Col_c2 := rtrim(Col_c2,1);
RETURN Col_c2;
END;
/
SQL> select distinct c1 ,get_c2(c1) cc2 from table;即可

关于oracle中纵向记录横向显示的问题举例说明 

最近在论坛里经常看到有朋友问这个问题,下面列举两个真实问答例子来说明 一下:

例一:

问:(贴子地址:http://community.csdn.net/Expert/topic/5722/5722287.xml?temp=9.182376E-02

表如下:
ID  NO   Name  Course  Score
1   001  赵      语文   80
2   001  赵      数学   40
3   001  赵      英语   60
4   002  李      语文   50
5   002  李      数学   30
6   003  唐      语文   20
Sql语句执行结果如下:
NO         Name         AllCouse                   总分
001         赵          语文,数学,英语            180
002         李          语文,数学                  80
003         唐          语文                        20

答:

如果本例中的学科数量是有限的并且是已知的,则可以使用遍历的方法,用一个sql语句来实现:

select no,name,yw||xx||yy AllCouse,"语文"+"数学"+"英语" 总分 from
(select no,name, case when 语文<>0 then '语文' end yw,case when 数学<>0 then ',数学' end xx,case when 英语<>0 then ',英语' end yy,
"语文","数学","英语" from
(select no,name ,nvl(sum("语文"),0) "语文",nvl(sum("数学"),0) "数学",nvl(sum("英语"),0) "英语"
from (select no,name,
case when Course='语文'  then nvl(SUM(score),0) end "语文",
case when Course='数学'  then nvl(SUM(score),0) end "数学",
case when Course='英语'  then nvl(SUM(score),0) end "英语"

from lht_test
group by no,name,course) t
group by no,name) tt) ttt

例二:

问:(贴子地址:http://community.csdn.net/Expert/topic/5762/5762748.xml?temp=7.850283E-02

table1(id varchar2(10), name varchar2(10))

id  name
1   aa
1   bb
1   cc
2   xx
3   yy
3   zz
...

想得到一个结果集如下:
id  names
1   aa+bb+cc
2   xx
3   yy+zz
...

答:

这种情况中,name的值是不可预知的,则只能通过存储过程来返回一个记录集来实现:

先定义一个记录集的type,
create or replace type t_name as object
(
         Id              number(10),   
         Name varchar2(20),
);
create or replace type t_tab_names as table
of t_name;

create or replace procedure p_test (p_tTab_names out t_tab_names) as
  i number;
  flag number;
  vtemp varchar2(100);
  tname t_name;
begin
  i:=0;
  p_tTab_names:=new t_tab_names();
  for v in (select id from table1 group by id) loop
     i:=i+1;
     flag:=0;
     for vv in (select id,name from table1 where id=v.id) loop
       if (flag=0) then
         vtemp:=vv.name;
       else
         vtemp:=vtemp||'+'||vv.name;
       end if;
       flag:=1;
     end loop;
    
     tname.id:=v.id;
     tname.name:=vtemp;
     p_tTab_names.extend;
     p_tTab_names(i):=tname;
  end loop;
 
  exception when others then
    dbms_output.put_line(sqlerrm);

end p_test;

DECODE函数实现列转行

在实际的开发当中有时候会有让你做统计、做报表。 比如在学生选课管理系统中让你写出所有的学生的每一门可的成绩,这时候我们写的SQL得出的结果一般是每一个学生选了好多课,其中每一个课和对应的成绩都是一行...
  • ch7543658
  • ch7543658
  • 2014年11月15日 16:24
  • 4435

oracle行列转换函数的使用

oracle 10g wmsys.wm_concat行列转换函数的使用: 首先让我们来看看这个神奇的函数wm_concat(列名),该函数可以把列值以","号分隔起来,并显示成一行,接下来上例子...
  • HUXU981598436
  • HUXU981598436
  • 2014年07月26日 23:25
  • 1624

Oracle 行转列小结

最近在工作中,对行转列进行了应用,在此做一个简单的小结。       转换过程如下:         1、创建表结构 CREATE TABLE RowToCol ( ID N...
  • u010924897
  • u010924897
  • 2015年07月26日 12:45
  • 4356

Oracle实现行列转换的方法分析

参考网址:http://m.jb51.net/article/90982.htm https://www.bbsmax.com/A/Vx5MOwGzNr/ https://segmentfault...
  • fengjiexyb
  • fengjiexyb
  • 2017年12月01日 11:51
  • 39

Oracle一个典型行列转换的几种实现方法

SQL> select * from t;          I A          D ———- ———- ——————- 1 b          2008-03-27 10:55:42 ...
  • e_wsq
  • e_wsq
  • 2012年05月12日 22:33
  • 347

Oracle典型行列转换的几种实现方法

假如有如下表,其中各个i值对应的行数是不定的SQL> select * from t; I A D———- ———- ——————-1 b 2008-03-27 10:55:421 a 2008-0...
  • mylitboy
  • mylitboy
  • 2011年03月08日 15:00
  • 683

oracle行列转换方法汇总

第一部分:行转列 新建一个表: CREATE TABLE HANG2LIE (   "ID" NUMBER, "NAME" VARCHAR2(20), "COURSE" ...
  • readerjun0314
  • readerjun0314
  • 2016年03月10日 11:09
  • 119

通过SQL语句实现行列转换的几种方法

  • 2017年12月18日 16:15
  • 28KB
  • 下载

ORACLE分析函数(6)--使用listagg实现行列转换

listagg函数是在oracle11g中新引入的聚合函数,当然也可以作为分析函数来使用。该函数可以基于分组内特定列的排序,将测量列进行拼接合并。...
  • joeadai
  • joeadai
  • 2013年10月18日 09:19
  • 1677

SQL SERVER 合并重复行,行列转换(sql server2000 里面如何实现oracle10g的 wmsys.wm_concat() 的功能?)

sql server2000 里面如何实现oracle10g的 wmsys.wm_concat() 的功能? 以及学习以下关键字: 一、行列转换关键字PIVOT与UNPIVOT的使用 二、OUTE...
  • stamSuper1
  • stamSuper1
  • 2013年04月25日 16:10
  • 1505
内容举报
返回顶部
收藏助手
不良信息举报
您举报文章:Oracle中实现行列转换的方法
举报原因:
原因补充:

(最多只允许输入30个字)