Home > PL/SQL > Using CAST and COLLECT to aggregate strings

Using CAST and COLLECT to aggregate strings

I think this is nifty:

SQL> select ename, deptno from emp;

ENAME DEPTNO
---------- ----------
KING 10
BLAKE 30
CLARK 10
JONES 20
SCOTT 20
FORD 20
SMITH 20
ALLEN 30
WARD 30
MARTIN 30
TURNER 30

ENAME DEPTNO
---------- ----------
ADAMS 20
JAMES 30
MILLER 10

14 rows selected.

SQL> create type table_of_names as table of varchar2(20);
2 /

Type created.

SQL> create or replace function to_string(p_table_in table_of_names)
2 return varchar2
3 is
4 l_return_string varchar2(2000);
5 begin
6 for x in 1..p_table_in.count
7 loop
8 l_return_string:=l_return_string||p_table_in(x);
9 if x <> p_table_in.count then
10 l_return_string:=l_return_string||',';
11 end if;
12 end loop;
13 return l_return_string;
14* end to_string;
SQL> /

Function created.

SQL> select to_string(cast(collect(ename) as table_of_names)) from emp
2 where deptno = 20;

TO_STRING(CAST(COLLECT(ENAME) AS TABLE_OF_NAMES))
--------------------------------------------------------------------------------

JONES,SCOTT,FORD,SMITH,ADAMS


Oracle Documentation on CAST:
http://download-east.oracle.com/docs/cd/B19306_01/server.102/b14200/functions016.htmOracle Documentation on COLLECT: http://download-east.oracle.com/docs/cd/B19306_01/server.102/b14200/functions024.htm#sthref1147

Advertisements
Categories: PL/SQL
  1. No comments yet.
  1. No trackbacks yet.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: