SELECT MAX (SUBSTR (SYS_CONNECT_BY_PATH (colb, ''), 2)) colb
FROM (SELECT colb, colc, LEAD (colc) OVER (ORDER BY colc) cold
FROM (SELECT colb, ROW_NUMBER () OVER (ORDER BY colb) colc
FROM (SELECT cname AS colb
FROM col
WHERE tname = UPPER ('表名'))))
START WITH cold IS NULL
CONNECT BY PRIOR colc = cold
FROM (SELECT colb, colc, LEAD (colc) OVER (ORDER BY colc) cold
FROM (SELECT colb, ROW_NUMBER () OVER (ORDER BY colb) colc
FROM (SELECT cname AS colb
FROM col
WHERE tname = UPPER ('表名'))))
START WITH cold IS NULL
CONNECT BY PRIOR colc = cold
http://community.csdn.net/Expert/topic/4161/4161213.xml?temp=.21513
select fields1, fields2, colc, lead(colc) over(partition by fields1 order by colc) cold from (
select fields1, fields2, row_number() over(order by fields1, fields2) colc from tab) )
start with cold is null
connect by prior colc=cold
group by fields1;
---------------------------------
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
---------- ----------
甲 风湿
甲 感冒
甲 眼睛患疾
乙 头痛
乙 腿残
丙 指头断
丙 鼻炎7 rows selectedExecuted in 0.016 secondsSQL> select c1,replace(max(sys_connect_by_path(c2,' ')),' ',null) c2 from
(
select c1,c2,rn,lead(rn) over(partition by c1 order by rn) rn1
from (select c1,c2,row_number() over(order by c1,c2 desc) rn from c)
)
start with rn1 is null
connect by rn1 = prior rn
group by c1C1 C2
---------- --------------------------------------------------------------
丙 鼻炎指头断
甲 风湿感冒眼睛患疾
乙 头痛腿残Executed in 0.015 secondsSQL>
select sys_connect_by_path(disease_desc,'/')
from (
select docuno, disease_desc, colc, lead(colc) over(partition by docuno order by colc) cold from (
select docuno, disease_desc, row_number() over(order by docuno, disease_desc) colc from lmd_test) )
start with cold is null
connect by prior colc= cold
;