Re: query to combine a column

On Jul 13, 8:44 pm, "Tim B" <nos..._at_someisp.ca> 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 = 8) ||
(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. Received on Fri Jul 13 2007 - 23:17:07 CDT