Re: get distinct values

From: Maxim Demenko <mdemenko_at_gmail.com>
Date: Fri, 12 Mar 2010 23:08:55 +0100
Message-ID: <4B9ABB77.9080906_at_gmail.com>

On 12.03.2010 22:58, webtourist wrote:
>> SELECT deptno,
> 2 substr(REPLACE(REPLACE(xmlagg(xmlelement("x", job) ORDER BY
> 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
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 Received on Fri Mar 12 2010 - 16:08:55 CST