类似wm_concat功能的SQL语句！

[liupeiran8]

wm_concat函数在合并列值的时候并不能按顺序合并，
比如表A MARK ID
A 1
A 2
A 3
wm_concat之后得出的值是1,3,2，如果要得出1,2,3这样按顺序的值应该怎么写SQL？
貌似应该用 OVER (PARTITION BY)来写，但是试了半天也没写出来，求大神。

[wangchangming]

SQL> WITH TAB AS
  2  (
  3    SELECT 'A' AS MARK,2 AS ID FROM DUAL
  4     UNION ALL
  5    SELECT 'A',1 FROM DUAL
  6     UNION ALL
  7    SELECT 'A',3 FROM DUAL
  8     UNION ALL
  9    SELECT 'B',3 FROM DUAL
 10     UNION ALL
 11    SELECT 'B',1 FROM DUAL
 12     UNION ALL
 13    SELECT 'B',2 FROM DUAL
 14  )
 15  SELECT *
 16    FROM (SELECT MARK,
 17                 WM_CONCAT(ID) OVER(PARTITION BY MARK ORDER BY ID ASC) ARRAYID
 18            FROM TAB
 19           ORDER BY LENGTH(ARRAYID) DESC)
 20   WHERE ROWNUM <= (SELECT COUNT(DISTINCT MARK) FROM TAB);
MARK ARRAYID
---- --------------------------------------------------------------------------------
A    1,2,3
B    1,2,3