SQL & PL/SQL

Hi,

I need help to find a way to take out distinct values from a table that have the same id.

Here is the example that I follow but this is based on a table that have just one unique value listed in the table. I need therefor a function that takes out distinct values. How will this function below then look like?

Example:

First we will set up a demo table. We have 2 columns A and B. We desire output in the
format:

A-value1 b-value1 b-value2 b-value3 ....
A-value2 b-value1 b-value2 ....


ask_tom@OSI1.WORLD> column t format a30
ask_tom@OSI1.WORLD> drop table t;
Table dropped.

ask_tom@OSI1.WORLD> create table t
2 ( a varchar2(25),
3 b varchar2(25)
4 );

Table created.

ask_tom@OSI1.WORLD> insert into t values ( '210','5000' );
ask_tom@OSI1.WORLD> insert into t values ( '210','5001' );
ask_tom@OSI1.WORLD> insert into t values ( '210','5002' );
ask_tom@OSI1.WORLD> insert into t values ( '220','6001' );
ask_tom@OSI1.WORLD> insert into t values ( '220','6002' );
ask_tom@OSI1.WORLD> commit;

Commit complete.

Our first implementation simply uses static sql to select all of the values for B from
T for a given A and string them together:

ask_tom@OSI1.WORLD> create or replace
2 function get_transposed( p_a in varchar2 )
3 return varchar2
4 is
5 l_str varchar2(2000) default null;
6 l_sep varchar2(1) default null;
7 begin
8 for x in ( select b from t where a = p_a ) loop
9 l_str := l_str || l_sep || x.b;
10 l_sep := '-';
11 end loop;
12 return l_str;
13 end;
14 /

Function created.

ask_tom@OSI1.WORLD>
ask_tom@OSI1.WORLD> select a, get_transposed( a ) t
2 from t
3 group by a
4 /

A T
------------------------- ------------------------------
210 5000-5001-5002
220 6001-6002


Hope my question was clear.

Best regards H