query to combine a column

I have a query that returns something like this, which is in a pl/sql
function:

id description col3

---------------------------------
123 dingo stuff123

357 anteater stuff357

357 aardvark stuff357

357 wombat stuff357

677 kangaroo stuff677

What I want is a query that will transform the results of the above query
like this:

id description col3

-------------------------------------------------------------
123 dingo stuff123

357 anteater$$aardvark$$wombat stuff357

677 kangaroo stuff677



I want to remove the duplicate case_nums and combine their descriptions. The
'$$' would be for use as a marker for splitting up the string inJava.

Any suggestions on how to do this, if it can be done?

Alternatively - This query is used to populate a cursor, which is returned
by the function.

Is there a way to make the transformation in pl/sql and still return a
cursor from the function?

On Jul 13, 8:44 pm, "Tim B" wrote:
> I have a query that returns something like this, which is in a pl/sql
> function:
>
> id description col3
>
> ---------------------------------
> 123 dingo stuff123
>
> 357 anteater stuff357
>
> 357 aardvark stuff357
>
> 357 wombat stuff357
>
> 677 kangaroo stuff677
>
> What I want is a query that will transform the results of the above query
> like this:
>
> id description col3
>
> -------------------------------------------------------------
> 123 dingo stuff123
>
> 357 anteater$$aardvark$$wombat stuff357
>
> 677 kangaroo stuff677
>
> I want to remove the duplicate case_nums and combine their descriptions. The
> '$$' would be for use as a marker for splitting up the string inJava.
>
> Any suggestions on how to do this, if it can be done?
>
> Alternatively - This query is used to populate a cursor, which is returned
> by the function.
>
> Is there a way to make the transformation in pl/sql and still return a
> cursor from the function?

Is there an upper limit of the number of records with the same ID?
Are you ordering them alphabetically within the group (because it
appears you are not, despite using aardvark as an example).

If there IS an upper limit (within reason, let's say 100), then a
simple query using various ranking functions will give you the
results. Look into the LEAD function, passing in 1,2,3,4,5.....100 as
the second parameter to retrieve data in the correct order and
grouping.

Alternatively you can use the row_number() analytical function and
partition by your ID number. I'll demo how this can be done on a table
with your results, but it could easily be altered to work on a
subquery returning your dataset above:



create table T (ID number, des varchar2(20), COL3 varchar2(20));
Table created.

insert into T values (123, 'dingo', 'stuff123');
1 row created.

insert into T values (357, 'anteater', 'stuff357');
1 row created.

insert into T values (357, 'aardvark', 'stuff357');
1 row created.

insert into T values (357, 'wombat', 'stuff357');
1 row created.

insert into T values (677, 'kangaroo', 'stuff677');
1 row created.

This shows the values of the row_number() function when partitioned
properly:

select ID, des, COL3,
row_number() over(partition by ID order by des asc) my_order
from T;


ID DES COL3 MY_ORDER
---------- -------------------- -------------------- ----------
123 dingo stuff123 1
357 aardvark stuff357 1
357 anteater stuff357 2
357 wombat stuff357 3
677 kangaroo stuff677 1

5 rows selected.

And this query will give you the concatenated results desired. I am
using an upper limit of 10 possible members with any particular ID
number. You may need to change this to another alternate number, you
should be able to get away with a few hundred (just keep the SQL
length below ~ 30K)

with W as
(
select ID, des, COL3,
row_number() over(partition by ID order by des asc) my_order
from T
)
select ID,
(select min(DES) from W W2 where W1.ID = W2.ID and MY_ORDER = 1) ||
(select decode(min(DES), NULL, NULL, '$$' || min(DES)) from W W2 where
W1.ID = W2.ID and MY_ORDER = 2) ||
(select decode(min(DES), NULL, NULL, '$$' || min(DES)) from W W2 where
W1.ID = W2.ID and MY_ORDER = 3) ||
(select decode(min(DES), NULL, NULL, '$$' || min(DES)) from W W2 where
W1.ID = W2.ID and MY_ORDER = 4) ||
(select decode(min(DES), NULL, NULL, '$$' || min(DES)) from W W2 where
W1.ID = W2.ID and MY_ORDER = 5) ||
(select decode(min(DES), NULL, NULL, '$$' || min(DES)) from W W2 where
W1.ID = W2.ID and MY_ORDER = 6) ||
(select decode(min(DES), NULL, NULL, '$$' || min(DES)) from W W2 where
W1.ID = W2.ID and MY_ORDER = 7) ||
(select decode(min(DES), NULL, NULL, '$$' || min(DES)) from W W2 where
W1.ID = W2.ID and MY_ORDER = ||
(select decode(min(DES), NULL, NULL, '$$' || min(DES)) from W W2 where
W1.ID = W2.ID and MY_ORDER = 9) ||
(select decode(min(DES), NULL, NULL, '$$' || min(DES)) from W W2 where
W1.ID = W2.ID and MY_ORDER = 10)
as MY_RESULT
from W W1
group by ID;


ID MY_RESULT
---------- --------------------------
123 dingo
677 kangaroo
357 aardvark$$anteater$$wombat

3 rows selected.

If you are using ADO and you cannot use the WITH statement on the
first line, put a "select(*) from" around the query.