把记录行列转换

with a as (select 1 id,'s' name,100 fee,'200711' month from dual
            union all
            select 1 id,'s' name,200 fee,'200710' month from dual
            union all
            select 1 id,'s' name,300 fee,'200709' month from dual
            union all
            select 2 id,'a' name,100 fee,'200711' month from dual
            union all
            select 2 id,'a' name,200 fee,'200710' month from dual
            union all
            select 2 id,'a' name,3000 fee,'200709' month from dual
            union all
            select 3 id,'m' name,1000 fee,'200709' month from dual
            )
 select id,name,max(decode(rn,1,month,null)) month1,
                max(decode(rn,1,fee,null)) fee1,
                max(decode(rn,2,month,null)) month2,
                max(decode(rn,2,fee,null)) fee2,
                max(decode(rn,3,month,null)) month3,
                max(decode(rn,3,fee,null)) fee3
 from (select id,name,month,fee,row_number()over(partition by id order by month) rn from a)
 group by id,name
 order by id
 / 

最简单的方法

with testa as (select 'a' || rownum q, 'a'  no
          from dual         
        connect by rownum < 4
        union
        select 'b' || rownum name, 'c'  rn
         from dual         
        connect by rownum <2
        union
        select 'c' || rownum name, 'd'  rn
         from dual         
        connect by rownum <5
        )     

select no,max(sys_connect_by_path(q,',')) from (
select no,q,
       row_number() over( order by no) rn,
       row_number() over(partition by no order by no) rn1
from testa
)
start with rn1=1
connect by rn-1=prior rn
group by no

 第二种方法错误

实现第三种方法

with a as (
     select trunc(dbms_random.value(1,20)) no, trunc(dbms_random.value(1,20)) q from dual
connect by rownum < 40
)
select a.no,to_char(q) from a
union all
select no,max(sys_connect_by_path(q,',')) from ( select l.no,l.q,l.rn+r.rn rn,rn1
from (
select no,q,
       row_number() over(order by no) rn,
       row_number() over(partition by no order by no) rn1
from a
) l,
(select no,rownum rn from (
select distinct no
from a
order by no
) ) r
where l.no=r.no
)
start with rn1=1
connect by rn-1=prior rn
group by no

10g以上的方法简单

WITH A AS (
SELECT 1123 ID,'2008-9-19' DAY,'9:7:47' TIME FROM DUAL UNION  
SELECT 1123 ID,'2008-9-19' DAY,'19:7:47' TIME FROM DUAL  UNION   
SELECT 1123 ID,'2008-9-19' DAY,'13:7:47' TIME FROM DUAL  UNION   
SELECT 1123 ID,'2008-9-19' DAY,'12:7:47' TIME FROM DUAL UNION   
SELECT 1123 ID,'2008-19-19' DAY,'12:7:47' TIME FROM DUAL
)
select ID,DAY,WMSYS.WM_CONCAT(TIME) TIME FROM A GROUP BY ID,DAY  

 10g以下

WITH A AS (
SELECT 1123 ID,'2008-9-19' DAY,'9:7:47' TIME FROM DUAL UNION  
SELECT 1123 ID,'2008-9-19' DAY,'19:7:47' TIME FROM DUAL  UNION   
SELECT 1123 ID,'2008-9-19' DAY,'13:7:47' TIME FROM DUAL  UNION   
SELECT 1123 ID,'2008-9-19' DAY,'12:7:47' TIME FROM DUAL UNION   
SELECT 1123 ID,'2008-19-19' DAY,'12:7:47' TIME FROM DUAL
)

select ID,DAY,MAX(SYS_CONNECT_BY_PATH(TIME,',')) TIME FROM  
(SELECT ID,DAY,TIME,ROW_NUMBER()OVER(PARTITION BY ID,DAY ORDER BY ID ) RN FROM A) 
START WITH RN=1  
CONNECT BY RN-1=PRIOR RN 
GROUP BY ID,DAY

 

8i写函数来实现

 

create or replace package hierarchy is
  type strtabletype is table of varchar2(4000) index by binary_integer;
  strtable strtabletype;
  type numtabletype is table of number index by binary_integer;
  numtable numtabletype;
  function sys_connect_by_path(p_level     in number,
                               p_value     in varchar2,
                               p_delimiter in varchar2 default ',')
    return varchar2;
  function sys_sum_by_path(p_level in number, p_value in number)
    return number;
  pragma restrict_references(sys_connect_by_path, wnds);
  pragma restrict_references(sys_sum_by_path, wnds);
end;
/

create or replace package body hierarchy is
  ls_ret varchar2(4000);
  ln_ret number;
  function sys_connect_by_path(p_level     in number,
                               p_value     in varchar2,
                               p_delimiter in varchar2 default ',')
    return varchar2 is
  begin
    strtable(p_level) := p_value;
    ls_ret := p_value;
    for i in reverse 1 .. p_level - 1 loop
      ls_ret := strtable(i) || p_delimiter || ls_ret;
    end loop;
    return ls_ret;
  end;

  function sys_sum_by_path(p_level in number, p_value in number)
    return number is
  begin
    numtable(p_level) := p_value;
    ln_ret := p_value;
    for i in reverse 1 .. p_level - 1 loop
      ln_ret := numtable(i) + ln_ret;
    end loop;
    return ln_ret;
  end;
end;
/

 

select rpad(' ', 4 * level, ' ') || ename emp_name,
       sal,
       hierarchy.sys_connect_by_path(level, sal, '/') sal_path,
       hierarchy.sys_sum_by_path(level, sal) sal_sum
  from emp
start with mgr is null
connect by prior empno = mgr;

转自http://www.cnblogs.com/gkl0818/archive/2009/03/01/1401039.html