example: ------------------------------------------------------------------------------------- 现有表A,结构及数据如下: Group Name 1 A 1 B 1 C 2 A 2 D 3 A 3 C现要求将相同Group的Name提出来合并到一个字段,输出如下结果: Group Name 1 ABC 2 AD 3 AC 1、 select "Group",max(sys_connect_by_path("Name") "Name" from ( select "Group","Name",rn,lead(rn) over(partition by "Group" order by rn) rn1 from (select "Group","Name",row_number() over(order by "Group","Name") rn from a) ) start with rn1 is null connect by rn1 = prior rn group by "Group" ; 2、 select "Group",max(sys_connect_by_path("Name") "Name" result from (select "Group","Name", (row_number() over(order by "Group","Name" desc) +dense_rank() over(order by "Group")) rn, max("Name") over(partition by "Group") name1 from a) start with "Name"= name1 connect by rn-1 = prior rn group by "Group" ;
/* SumString函数的5各参数的意思分别是: 1. 表名; 2. 你想Group BY的字段名 3. 你想sum的那个字段名 4. Group By字段的值 5. Sum字符串时的分隔符 */CREATE OR REPLACE FUNCTION SumString( I_TableName IN VARCHAR2 , I_GroupColName IN VARCHAR2 , I_ResultColName IN VARCHAR2 , I_GroupColValue IN VARCHAR2 , I_Separator IN VARCHAR2 ) RETURN VARCHAR2 IS TYPE T_Cur IS REF CURSOR ; C_Cur T_Cur ; V_Sql VARCHAR2(2000) ; V_Result VARCHAR2(2000) ; V_Tmp VARCHAR2(200) ; V_Cnt NUMBER := 0 ; BEGIN V_Result := '' ; V_Sql := 'SELECT '|| I_ResultColName ||' FROM '|| I_TableName ||' WHERE '|| I_GroupColName || ' = '''|| I_GroupColValue || '''' ; OPEN C_Cur FOR V_Sql ; LOOP FETCH C_Cur INTO V_Tmp ; EXIT WHEN C_Cur%NOTFOUND ; IF V_Cnt = 0 THEN V_Result := V_Tmp ; ELSE V_Result := V_Result || I_Separator || V_Tmp ; END IF ; V_Cnt := V_Cnt + 1 ; END LOOP ; CLOSE C_Cur ;
RETURN V_Result ; END SUMSTRING; SELECT id, SumString( '表', 'id' , 'name' , 'id' , '-' ) SUM_B FROM 表 GROUP BY id;
select ID,substr(max(sys_connect_by_path(MEMO,',')),2) MEMO from ( select ID,MEMO,rn,lead(rn) over(partition by ID order by rn) rn1 from (select ID,MEMO,row_number() over(order by ID desc) rn from 表) ) start with rn1 is null connect by rn1 = prior rn group by ID
-------------------------------------------------------------------------------------
现有表A,结构及数据如下:
Group Name
1 A
1 B
1 C
2 A
2 D
3 A
3 C现要求将相同Group的Name提出来合并到一个字段,输出如下结果:
Group Name
1 ABC
2 AD
3 AC
1、
select "Group",max(sys_connect_by_path("Name") "Name" from
(
select "Group","Name",rn,lead(rn) over(partition by "Group" order by rn) rn1
from (select "Group","Name",row_number() over(order by "Group","Name") rn from a)
)
start with rn1 is null
connect by rn1 = prior rn
group by "Group"
;
2、
select "Group",max(sys_connect_by_path("Name") "Name" result
from (select "Group","Name",
(row_number() over(order by "Group","Name" desc)
+dense_rank() over(order by "Group")) rn,
max("Name") over(partition by "Group") name1
from a)
start with "Name"= name1
connect by rn-1 = prior rn
group by "Group"
;
SumString函数的5各参数的意思分别是:
1. 表名;
2. 你想Group BY的字段名
3. 你想sum的那个字段名
4. Group By字段的值
5. Sum字符串时的分隔符
*/CREATE OR REPLACE FUNCTION SumString(
I_TableName IN VARCHAR2 ,
I_GroupColName IN VARCHAR2 ,
I_ResultColName IN VARCHAR2 ,
I_GroupColValue IN VARCHAR2 ,
I_Separator IN VARCHAR2
)
RETURN VARCHAR2 IS
TYPE T_Cur IS REF CURSOR ;
C_Cur T_Cur ;
V_Sql VARCHAR2(2000) ;
V_Result VARCHAR2(2000) ;
V_Tmp VARCHAR2(200) ;
V_Cnt NUMBER := 0 ;
BEGIN
V_Result := '' ;
V_Sql := 'SELECT '|| I_ResultColName ||' FROM '|| I_TableName ||' WHERE '|| I_GroupColName || ' = '''|| I_GroupColValue || '''' ;
OPEN C_Cur FOR V_Sql ;
LOOP
FETCH C_Cur INTO V_Tmp ;
EXIT WHEN C_Cur%NOTFOUND ;
IF V_Cnt = 0 THEN
V_Result := V_Tmp ;
ELSE
V_Result := V_Result || I_Separator || V_Tmp ;
END IF ;
V_Cnt := V_Cnt + 1 ;
END LOOP ;
CLOSE C_Cur ;
RETURN V_Result ;
END SUMSTRING; SELECT id, SumString( '表', 'id' , 'name' , 'id' , '-' ) SUM_B FROM 表 GROUP BY id;
(
select ID,MEMO,rn,lead(rn) over(partition by ID order by rn) rn1
from (select ID,MEMO,row_number() over(order by ID desc) rn from 表)
)
start with rn1 is null connect by rn1 = prior rn
group by ID