Prev: Oracle 10g on Solaris 10 non-global zones with asynchronous I/O
Next: Network Configuration Assistant error
From: webtourist on 12 Mar 2010 16:58
> 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
From: Maxim Demenko on 12 Mar 2010 17:08
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
From: John Hurley on 12 Mar 2010 18:42
On Mar 12, 4:58 pm, webtourist <webtour...(a)gmail.com> wrote:

snip

> 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

Are you running 7.3.4?
From: Shakespeare on 14 Mar 2010 05:50
Op 13-3-2010 0:42, John Hurley schreef:
> On Mar 12, 4:58 pm, webtourist<webtour...(a)gmail.com> wrote:
>
> snip
>
>> 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
>
> Are you running 7.3.4?

Was xmlagg in that version?

Shakespeare
From: webtourist on 15 Mar 2010 14:21
On Mar 12, 7:42 pm, John Hurley <johnbhur...(a)sbcglobal.net> wrote:
> Are you running 7.3.4?


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


 |  Next  |  Last
Pages: 1 2
Prev: Oracle 10g on Solaris 10 non-global zones with asynchronous I/O
Next: Network Configuration Assistant error