job), '</x>'), '<x>', ' '), 2) job_list
3 FROM emp
4 GROUP BY deptno
5 ORDER BY deptno
6/

DEPTNO JOB_LIST
------ ----------------------------------------------------
10 CLERK MANAGER PRESIDENT
20 ANALYST ANALYST CLERK CLERK MANAGER
30 CLERK MANAGER SALESMAN SALESMAN SALESMAN SALESMAN



How can I get result like this: (distinct names in "job_list") ?


DEPTNO JOB_LIST
------ ----------------------------------------------------
10 CLERK MANAGER PRESIDENT
20 ANALYST CLERK MANAGER
30 CLERK MANAGER SALESMAN

On 12.03.2010 22:58, webtourist wrote:
Silly approach:

select deptno,
substr(replace(replace(xmlagg(xmlelement("x", job) order by job),
'</x>'), '<x>', ' '), 2) job_list
from (select unique deptno,job from emp) emp
group by deptno
order by deptno

?

Best regards

Maxim

On Mar 12, 4:58*pm, webtourist <webtour... (AT) gmail (DOT) com> wrote:

snip

Are you running 7.3.4?

Op 13-3-2010 0:42, John Hurley schreef:
Was xmlagg in that version?

Shakespeare

On Mar 12, 7:42*pm, John Hurley <johnbhur... (AT) sbcglobal (DOT) net> wrote:

no, it's too advanced for us....we're on 10gR2

solution found:

SELECT deptno,
substr(REPLACE(REPLACE(xmlagg(xmlelement("x", job) ORDER BY
job), '</x>'), '<x>', ' '), 2) job_list,
wmsys.wm_concat(DISTINCT job) job_list_distinct
FROM emp
GROUP BY deptno
ORDER BY deptno



I win