相同IDd的字符的?接 name value
---------------------------------
1 A
2 B
1 C
3 E
2 F
1 G?果
name value
-----------------------
1 A,C,G
2 B,F
3 E--生成??数据
create table tname(name int,value varchar(10))
insert into tname select 1,'A'
insert into tname select 2,'B'
insert into tname select 1,'C'
insert into tname select 3,'E'
insert into tname select 2,'F'
insert into tname select 1,'G'
go--?建用?定?函数
create function f_str(@name int)
returns varchar(8000)
as
begin
declare @ret varchar(8000)
set @ret = ''
select @ret = @ret + ',' + value from tname where name = @name
set @ret = stuff(@ret,1,1,'')
return @ret
end
go--?行??
select name,value=dbo.f_str(name) from tname group by name order by name--?出?果
/*
name value
---- -----
1 A,C,G
2 B,F
3 E
*/--?除???境
drop function f_str
drop table tname
go
---------------------------------
1 A
2 B
1 C
3 E
2 F
1 G?果
name value
-----------------------
1 A,C,G
2 B,F
3 E--生成??数据
create table tname(name int,value varchar(10))
insert into tname select 1,'A'
insert into tname select 2,'B'
insert into tname select 1,'C'
insert into tname select 3,'E'
insert into tname select 2,'F'
insert into tname select 1,'G'
go--?建用?定?函数
create function f_str(@name int)
returns varchar(8000)
as
begin
declare @ret varchar(8000)
set @ret = ''
select @ret = @ret + ',' + value from tname where name = @name
set @ret = stuff(@ret,1,1,'')
return @ret
end
go--?行??
select name,value=dbo.f_str(name) from tname group by name order by name--?出?果
/*
name value
---- -----
1 A,C,G
2 B,F
3 E
*/--?除???境
drop function f_str
drop table tname
go
from Table1) connect by prior m=n start with n=0;
-----------
1
2
3
4
5
6
77 rows selectedExecuted in 0.031 secondsSQL> select substr(max(sys_connect_by_path(c3,',')),2) result from
(
select c1,c3,rn,lead(rn) over(partition by c1 order by rn) rn1
from (
select 1 c1,c3,row_number() over(order by c3 desc) rn from c
)
)
start with rn1 is null
connect by rn1 = prior rn
group by c1RESULT
--------------------------------------------------------------------------------
1,2,3,4,5,6,7Executed in 0.016 secondsSQL>
from table
start with a=1
connect by prior a=a-1
----------
1
2
3
4
5
6
7已选择7行。SQL> select substr(max(sys_connect_by_path(id,',')),2) result
2 from (select id,rownum rn from a)
3 start with rn = 1
4 connect by rn = prior rn+1
5 /RESULT
--------------------------------------------------------------------------------
1,2,3,4,5,6,7分组连接参考:http://community.csdn.net/Expert/topic/4161/4161213.xml?temp=.4358637
create type CatStringImpl as object
(
catstring VARCHAR2(4000), -- 返回的字符串
static function ODCIAggregateInitialize(sctx IN OUT CatStringImpl)
return number,
member function ODCIAggregateIterate(self IN OUT CatStringImpl,
value IN varchar2) return number,
member function ODCIAggregateTerminate(self IN CatStringImpl,
returnValue OUT varchar2, flags IN number) return number,
member function ODCIAggregateMerge(self IN OUT CatStringImpl,
ctx2 IN CatStringImpl) return number
);
create or replace type body CatStringImpl is
static function ODCIAggregateInitialize(sctx IN OUT CatStringImpl )
return number is
begin
sctx := CatStringImpl('');
return ODCIConst.Success;
end;
member function ODCIAggregateIterate(self IN OUT CatStringImpl, value IN varchar2)
return number is
begin
self.catstring := self.catstring || ',' || value;
return ODCIConst.Success;
end;
member function ODCIAggregateTerminate(self IN CatStringImpl, returnValue OUT
varchar2, flags IN number) return number is
begin
returnValue := self.catstring;
return ODCIConst.Success;
end;
member function ODCIAggregateMerge(self IN OUT CatStringImpl , ctx2 IN
CatStringImpl ) return number is
begin
self.catstring = self.catstring || ',' || ctx2.catstring ;
return ODCIConst.Success;
end;
end;
建函数
create function catstr( input varchar2 ) return varchar2 PARALLEL_ENABLE AGGREGATE USING CatStringImpl ;
使用
select catstr( code ) , code from tb group by code 就可以将字符串串接
我用的是PL/SQL,连的是ORACLE8I数据库,提示“无效列名”,应该是不认“sys_connect_by_path”?!
/******************************************************************************
PURPOSE: REVISIONS:
Ver Date Author Description
--------- ---------- --------------- ------------------------------------
1.0 2005-9-5 Fred zhang 1. Created this procedure.
******************************************************************************/
Result varchar2(32700);
cursor C_descs is select distinct c_desc from Gp_Scrap_Body where gatepass_no=GatePassNo;
Return_C_descs varchar2(32700);
begin
For rec in C_descs loop
if rec.c_desc is not null then
Return_C_descs:=Return_C_descs||rec.c_desc;
end if;
end loop;
Result :=Return_C_descs;
return(Result);
end Get_C_descs;