17,382
社区成员




SQL> select * from ttt;
CATEID USERID
---------- ----------
cate1 user1
cate1 user2
cate2 user2
SQL>
SQL> select cateid, substr(max(sys_connect_by_path(userid, ',')), 2) users
2 from (select cateid,
3 userid,
4 rn + row_number() over(order by rn) rn1,
5 row_number() over(partition by rn order by rn) rn2
6 from (select a.cateid, a.userid, rn
7 from ttt a
8 left join (select cateid, rownum rn
9 from (select distinct cateid from ttt)) b on a.cateid =
10 b.cateid
11 order by 1, 2))
12 start with rn2 = 1
13 connect by prior rn1 = rn2
14 group by cateid
15 ;
CATEID USERS
---------- --------------------------------------------------------------------------------
cate1 user1,user2
cate2 user2
select cateid,wmsys.wm_concat(userid) users from tableA group by cateid;
--to linzhangs:少了group by cateid可是不行的啊?!
select cateid,wmsys.wm_concat(userid) users from tablename