Java Development Tools

SQL> select deptno,
  2    replace( replace( replace(
  3      XMLQUERY('for $cc in ora:view("emp")
let $ename:=$cc/ROW/ENAME/text()
where $cc/ROW/DEPTNO/number()=$deptno/d/number()
return <e>{$ename}</e>'
  4      passing by value xmltype('<d>'||deptno||'</d>') as "deptno"
  5      returning content
  6    ),'</e><e>', ','),'<e>'),'</e>') enames
  7  from dept
    DEPTNO ENAMES
---------- --------------------------------------
        10 CLARK,KING,MILLER
        20 SMITH,JONES,SCOTT,ADAMS,FORD
        30 ALLEN,WARD,MARTIN,BLAKE,TURNER,JAMES
        40

http://www.oracle-base.com/articles/10g/StringAggregationTechniques.php

Hi,
Thanks for your response.

I missed out to specify my database version. I am working on below specified version:
Oracle8i Enterprise Edition Release 8.1.7.4.0 - Production
PL/SQL Release 8.1.7.4.0 - Production

and moreover without using dept table is it possible to display all the employee names in single line with "," as delimiter.

like a sql query which displays all the employee names present in employee table in a single string with "," as delimiter.

Thanks!

Naveen.

http://asktom.oracle.com/pls/ask/f?p=4950:8:::::F4950_P8_DISPLAYID:229614022562

SQL> select deptno,
  2      max(substr(sys_connect_by_path(ename,','),2)) emps
  3  from
  4      (
  5      select e.deptno, e.ename,
  6          row_number() over (
  7              partition by e.deptno order by e.ename
  8              ) curr,
  9          row_number() over (
 10              partition by e.deptno order by e.ename
 11              ) - 1 prev
 12      from emp e, dept d
 13      where e.deptno = d.deptno
 14      )
 15  start  with curr = 1
 16  connect by prior curr = prev
 17      and prior deptno = deptno
 18  group by deptno
 19  /

 DEPTNO EMPS
------- ----------------------------------------
     10 CLARK,KING
     20 ADAMS,FORD,JONES,SCOTT,SMITH
     30 ALLEN,BLAKE,JAMES,MARTIN,TURNER,WARD
     40 MILLER

3360,
sys_connect_by_path does not work on 8i, it was introduced in 9i

Can any one help me out in this ............ Plzzzzz

Did you read the link posted above by Jens?
Did you try the solution provided there?

Hi,
I need the output as below :
EMPS
----------------------------------------
CLARK,KING,ADAMS,FORD,JONES,SCOTT,SMITH,ALLEN,BLAKE,JAMES,MARTIN,TURNER,WARD, MILLER

The solution given Jens displays department number wise names , however i want all names to be displayed as mentione above irrespective of department number

Did you upgrade yet?

I am using oracle-8i, is there any possibility to get the result as specified using oracle 8i

create table KeyList(
Key char(3),
Val char(2),
primary key (Key,Val));

insert into KeyList values('AAA','12');
insert into KeyList values('AAA','2A');
insert into KeyList values('AAA','32');
insert into KeyList values('AAA','44');
insert into KeyList values('BBB','11');
insert into KeyList values('BBB','33');
insert into KeyList values('BBB','8S');
commit;

col ConcatVal for a20

select Key,
substr(
replace(
    replace(XMLAgg(XMLElement("dummy",Val) order by Val),'</dummy>'),
    '<dummy>',',')
,2) as ConcatVal
from KeyList
group by Key;

select Key,max(SubStr(sys_connect_by_path(Val,','),2)) as ConcatVal
from (select Key,Val,
      Lag(RowID) over(partition by Key order by Val) as LagRowID
      from KeyList)
start with LagRowID is null
connect by Prior RowID = LagRowID
group by Key;

OracleSQLPuzzle
http://www.geocities.jp/oraclesqlpuzzle

Dear Naveenhks

select 'empno '||empno||','||chr(15) from emp;

Try this.

Ranga

hi naveen, try this
SQL> create table tst ( n varchar2(100);
SQL> declare
H varchar2(100):=' ' ;
begin
for R in (select ename from emp) loop
H:=R.ENAME || ' , ' || H ;
end loop;
insert into tst values(H);
end;
/
SQL> SELECT * FROM TST;
MILLER,FORD,JAMES,ADAMS,TURNER,KING,SCOTT,CLARK,BLAKE,MARTIN,JONES,WARD,ALLEN,SMITH
----- is it what u need ?????

Is this what you are looking for?

SQL> ;
  1  select max(substr(sys_connect_by_path(rtrim(ename), ','), 2, 200)) conc
  2  from (select ename, rownum rn
  3                     from emp)
  4  start with rn = 1
  5* connect by rn = prior rn +1
SQL> /

CONC
----------------------------------------------------------------------------------------------------
KING,BLAKE,CLARK,JONES,MARTIN,ALLEN,TURNER,JAMES,WARD,FORD,SMITH,SCOTT,ADAMS,MILLER

SQL> 

Oh! I didn't see if it doesn't work on 8i.

Cheers
Sarma.

Message was edited by:
Radhakrishna Sarma

Naveen !!! Try this one also.
SQL> set serveroutput on
SQL>declare
v varchar2(100):='';
begin
for r in (select ename from emp order by sal) loop
v:=r.ename|| ',' ||v;
end loop;
dbms_output.put_line(v);
end;
/
--------------
KING,FORD,SCOTT,JONES,BLAKE,CLARK,ALLEN,TURNER,MILLER,MARTIN,WARD,ADAMS,JAMES,SMITH

Its Nice

Thank you
Raj Deep.A

I tried the fully generic string_agg function on Oracle 9.2, my output has a space between each character, could that be because my input is an NVARCHAR2?

SQL> select string_agg(licensenumb)
2 from clbce.avgrid
3 group by addby;

STRING_AGG(LICENSENUMB)
--------------------------------------------------------------------------------

a s d f a s f a s d g a s e r, k j h k
A 1 2 3 A B C, 3 P R Y 0 8 6

SQL> column plate format a50
SQL> select string_agg(licensenumb) as plate
2 from clbce.avgrid
3 group by addby;

PLATE
--------------------------------------------------

a s d f a s f a s d g a s e r, k j h k
A 1 2 3 A B C, 3 P R Y 0 8 6

SQL> describe clbce.avgrid
Name Null? Type
----------------------------------------- -------- ----------------------------
AVGRIDKEY NOT NULL NUMBER(9)
CASEDETAILKEY NUMBER(9)
ADDDTTM DATE
ADDBY NVARCHAR2(30)
MODBY NVARCHAR2(30)
MODDTTM DATE
MAKEOFCAR NVARCHAR2(10)
MODELOFCAR NVARCHAR2(10)
COMPLIANCEDATE DATE
LICENSENUMB NVARCHAR2(15)
VIN NVARCHAR2(25)

SQL>

7/5/2007

Hi,
Thanks for your response.

I missed out to specify my database version. I am
working on below specified version:
Oracle8i Enterprise Edition Release 8.1.7.4.0 -
Production
PL/SQL Release 8.1.7.4.0 - Production

and moreover without using dept table is it possible
to display all the employee names in single line with
"," as delimiter.

like a sql query which displays all the employee
names present in employee table in a single string
with "," as delimiter.

Thanks!

Naveen.

Here it is

Connected to:
Oracle8i Enterprise Edition Release 8.1.7.4.1 - Production
With the Partitioning option
JServer Release 8.1.7.4.1 - Production

SQL> set pagesize 9999;
SQL>
SQL> CREATE OR REPLACE
  2  type string_table as table of varchar2(4000)
  3  /

Type created.

SQL>
SQL> CREATE OR REPLACE
  2  FUNCTION concat_strings (
  3      strtab IN string_table,
  4      sep IN varchar2
  5  )
  6  RETURN
  7      varchar2
  8  IS
  9      outval varchar2(4000);
 10  BEGIN
 11      outval := null;
 12      if (( strtab is not null )and (strtab.count > 0 ) ) then
 13         for i in strtab.first .. strtab.last loop
 14                 if ( outval is null ) then
 15                 outval := strtab(i);
 16                 else
 17                     if sep is not null then
 18                         outval:=outval||sep||strtab(i);
 19                     else
 20                             outval:=outval||strtab(i);
 21                             end if;
 22             end if;
 23             end loop;
 24             end if;
 25      return outval;
 26  END;
 27  /

Function created.

SQL> drop table KeyList
  2  /

Table dropped.

SQL> create table KeyList(
  2  Key char(3),
  3  Val char(2),
  4  primary key (Key,Val));

Table created.

SQL>
SQL>
SQL>
SQL> insert into KeyList values('AAA','12');

1 row created.

SQL> insert into KeyList values('AAA','2A');

1 row created.

SQL> insert into KeyList values('AAA','32');

1 row created.

SQL> insert into KeyList values('AAA','44');

1 row created.

SQL> insert into KeyList values('BBB','11');

1 row created.

SQL> insert into KeyList values('BBB','33');

1 row created.

SQL> insert into KeyList values('BBB','8S');

1 row created.

SQL> commit;

Commit complete.

SQL>
SQL> SELECT CONCAT_STRINGS( CAST (
2 MULTISET (
3 SELECT B.VAL
4 FROM KEYLIST B
5 WHERE B.key = A.KEY
6 ORDER BY B.VAL
7 ) AS STRING_TABLE
8 ),',') as output
9 FROM (
10 SELECT DISTINCT KEY
11 FROM KeyList
12 ) A
13 /

OUTPUT
---------------

12,2A,32,44
11,33,8S

SQL>

Bye Alessandro