有下表tablename xz 黄继伟 S09
黄继伟 S09
廖永华 SB1
廖永华 SA9求助各位:
如何能将name相同的xz值连接在一起。
希望的结果如下:
name xz 黄继伟 S09,S09
廖永华 SB1,SA9谢谢各位!
黄继伟 S09
廖永华 SB1
廖永华 SA9求助各位:
如何能将name相同的xz值连接在一起。
希望的结果如下:
name xz 黄继伟 S09,S09
廖永华 SB1,SA9谢谢各位!
select name,wm_concat(xz) from table group by name;
SELECT user_name, TRANSLATE(LTRIM(text, '/'), '*/', '*,') researcherList
FROM (SELECT ROW_NUMBER() OVER(PARTITION BY user_name ORDER BY user_name, lvl DESC) rn,
user_name,
text
FROM (SELECT user_name,
LEVEL lvl,
SYS_CONNECT_BY_PATH(user_id, '/') text
FROM (SELECT user_name,
user_id as user_id,
ROW_NUMBER() OVER(PARTITION BY user_name ORDER BY user_name, user_id) x
FROM sys_bas_user
ORDER BY user_name, user_id) a
CONNECT BY user_name = PRIOR user_name
AND x - 1 = PRIOR x))
WHERE rn = 1
ORDER BY user_name;这里的返回结果是user_name相同的把user_id用,隔开放一行
你可以参照一下,
当然高版本oracle可以用1楼的方法,简洁多了
from (select a, b, row_number() over(partition by a order by b) rn from t2)
start with rn = 1
connect by rn-1 = prior rn
group by a