使用 WMSYS.WM_CONCAT 进行行列转换可以解决。以下例子对你很有帮助SQL> select version from v$instance;VERSION ----------------- 10.2.0.1.0SQL> SQL> create table IDTABLE 2 ( 3 id number, 4 val varchar2(20) 5 ) 6 ;Table createdSQL> SQL> insert into IDTABLE (ID, VAL) 2 values (10, 'abc');1 row inserted SQL> insert into IDTABLE (ID, VAL) 2 values (10, 'abc');1 row inserted SQL> insert into IDTABLE (ID, VAL) 2 values (10, 'def');1 row inserted SQL> insert into IDTABLE (ID, VAL) 2 values (10, 'def');1 row inserted SQL> insert into IDTABLE (ID, VAL) 2 values (20, 'ghi');1 row inserted SQL> insert into IDTABLE (ID, VAL) 2 values (20, 'jkl');1 row inserted SQL> insert into IDTABLE (ID, VAL) 2 values (20, 'mno');1 row inserted SQL> insert into IDTABLE (ID, VAL) 2 values (20, 'mno');1 row insertedSQL> select id,val from idtable; ID VAL ---------- -------------------- 10 abc 10 abc 10 def 10 def 20 ghi 20 jkl 20 mno 20 mno8 rows selectedSQL> commit;Commit completeSQL> SQL> SELECT ID, WMSYS.WM_CONCAT(VAL) AS ENAMES 2 FROM IDTABLE 3 GROUP BY ID; ID ENAMES ---------- -------------------------------------------------------------------------- 10 abc,abc,def,def 20 ghi,jkl,mno,mnoSQL> SQL> SELECT ID, WMSYS.WM_CONCAT(DISTINCT VAL) AS ENAMES 2 FROM IDTABLE 3 GROUP BY ID 4 ORDER BY ID; ID ENAMES ---------- -------------------------------------------------------------------------- 10 abc,def 20 ghi,jkl,mnoSQL>
10G数据库有这个: WMSYS.WM_CONCAT 10以下版本用这个: select 部門編號,部門名稱, trim(both ',' from max(sys_connect_by_path(備註, ','))) from (select t.部門編號, t.部門名稱, row_number() over(partition by 部門編號 order by 備註) rn from emp t) tt start with rn = 1 connect by prior rn = rn - 1 group by 部門編號
上面的有点问题 ,这个可以了:select deptno, trim(both ',' from max(sys_connect_by_path(ename, ','))) from (select t.deptno, t.ename, row_number() over(partition by deptno order by empno) rn from emp t) tt start with rn = 1 connect by prior rn = rn - 1 and tt.deptno=prior tt.deptno group by deptno
使用decode函数或case when语句
select 部門編號, 部門名稱, wm_concat(備註) from tb group by 部門編號,部門名稱
我用的是Oracle 8i,没有sys_connect_by_path,自己创建了个方法,但执行时会报错: “若设有诸如distinct, group by等,将无法对视规表只用connect by”。sys_connect_by_path 函数如下: 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 * FROM (SELECT DEPTID, DEPT_NA, GET_STR(DEPTID) REMARK FROM TEST) GROUP BY DEPTID,DEPT_NA,REMARKCREATE OR REPLACE FUNCTION "GET_STR" (v_pnl varchar2) return varchar2 is Col_c2 varchar2(800); begin Col_c2 := ''; for cur in (SELECT REMARK FROM TEST where pnl=v_pnl group by REMARK) loop Col_c2 := Col_c2||cur.REMARK ||'\r\n'; end loop; return Col_c2; end;我用这种方法就OK ,哈哈
-----------------
10.2.0.1.0SQL>
SQL> create table IDTABLE
2 (
3 id number,
4 val varchar2(20)
5 )
6 ;Table createdSQL>
SQL> insert into IDTABLE (ID, VAL)
2 values (10, 'abc');1 row inserted
SQL> insert into IDTABLE (ID, VAL)
2 values (10, 'abc');1 row inserted
SQL> insert into IDTABLE (ID, VAL)
2 values (10, 'def');1 row inserted
SQL> insert into IDTABLE (ID, VAL)
2 values (10, 'def');1 row inserted
SQL> insert into IDTABLE (ID, VAL)
2 values (20, 'ghi');1 row inserted
SQL> insert into IDTABLE (ID, VAL)
2 values (20, 'jkl');1 row inserted
SQL> insert into IDTABLE (ID, VAL)
2 values (20, 'mno');1 row inserted
SQL> insert into IDTABLE (ID, VAL)
2 values (20, 'mno');1 row insertedSQL> select id,val from idtable; ID VAL
---------- --------------------
10 abc
10 abc
10 def
10 def
20 ghi
20 jkl
20 mno
20 mno8 rows selectedSQL> commit;Commit completeSQL>
SQL> SELECT ID, WMSYS.WM_CONCAT(VAL) AS ENAMES
2 FROM IDTABLE
3 GROUP BY ID; ID ENAMES
---------- --------------------------------------------------------------------------
10 abc,abc,def,def
20 ghi,jkl,mno,mnoSQL>
SQL> SELECT ID, WMSYS.WM_CONCAT(DISTINCT VAL) AS ENAMES
2 FROM IDTABLE
3 GROUP BY ID
4 ORDER BY ID; ID ENAMES
---------- --------------------------------------------------------------------------
10 abc,def
20 ghi,jkl,mnoSQL>
10以下版本用这个:
select 部門編號,部門名稱, trim(both ',' from max(sys_connect_by_path(備註, ',')))
from (select t.部門編號,
t.部門名稱,
row_number() over(partition by 部門編號 order by 備註) rn
from emp t) tt
start with rn = 1
connect by prior rn = rn - 1
group by 部門編號
from (select t.deptno,
t.ename,
row_number() over(partition by deptno order by empno) rn
from emp t) tt
start with rn = 1
connect by prior rn = rn - 1
and tt.deptno=prior tt.deptno
group by deptno
group by 部門編號,部門名稱
“若设有诸如distinct, group by等,将无法对视规表只用connect by”。sys_connect_by_path 函数如下:
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;
is
Col_c2 varchar2(800);
begin
Col_c2 := '';
for cur in (SELECT REMARK FROM TEST where pnl=v_pnl group by REMARK) loop
Col_c2 := Col_c2||cur.REMARK ||'\r\n';
end loop;
return Col_c2;
end;我用这种方法就OK ,哈哈