把记录行列转换

最新推荐文章于 2025-01-02 15:38:44 发布

原创最新推荐文章于 2025-01-02 15:38:44 发布 · 654 阅读

0 ·

CC 4.0 BY-SA版权

文章标签：

#path #hierarchy #null #function #integer #table

Oracle 专栏收录该内容

21 篇文章

订阅专栏

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以下

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