temp_astaff_name number
张三 289
张三 145
张三 876
张三 908
李四 123
李四 324
李四 556
... ...想Insert temp_bstaff_name number
张三 289//145//876//908
李四 123//324//556
.. ..大致意思是把A表中的NUMBER进行串联 插入B表,请问大神该如何实现? staff_name 拥有的Number数是不确定的,谢谢
张三 289
张三 145
张三 876
张三 908
李四 123
李四 324
李四 556
... ...想Insert temp_bstaff_name number
张三 289//145//876//908
李四 123//324//556
.. ..大致意思是把A表中的NUMBER进行串联 插入B表,请问大神该如何实现? staff_name 拥有的Number数是不确定的,谢谢
(
select '张三' s1, '289' s2 from dual union all
select '张三','145' from dual union all
select '张三','876' from dual union all
select '张三','908' from dual union all
select '李四','123' from dual union all
select '李四','324' from dual union all
select '李四','556' from dual
)
select s1,wm_concat(s2) from a group by s1
wm_concat在9i下不适用的,在9i中如下sql:
SELECT staff_name, LTRIM(MAX(SYS_CONNECT_BY_PATH("number", '//')), '//') "number"
FROM (SELECT "number", staff_name, ROW_NUMBER() OVER(PARTITION BY staff_name ORDER BY "number" DESC) RN
FROM temp_a)
START WITH RN = 1
CONNECT BY RN - 1 = PRIOR RN
AND staff_name = PRIOR staff_name
GROUP BY staff_name;STAFF_NAME number
---------- -------------------------------------------------------------------
李四 556//324//123
张三 908//876//289//145