Returning a single column of rows in one cell

[t16turbo]

If I have the SQL statement

SELECT
dsti_proj_ref
FROM
odf_ca_project

how can I have each row of this in one cell.

lets say the results of the above query returns 5 rows:

Proj123
Proj345
Proj456
Proj567
Proj678

how can I write a select statement to give me:

Proj123Proj345Proj456Proj567Proj678

is this possible?

thanks in advance

 

[SantaMufasa]

(Turbo, Didn't we do something else with the "odf_ca_project" table on April 25 of this year?)

In any case, there are multiple methods...some easier than others. The easiest method is with a user-defined function.

Tell us more about your scenario:

1) Since your code, above has no WHERE clause, I presume that you want all row instances of "dsti_proj_ref"
from the "odf_ca_project" table, correct?

2) If so, is the number of rows in "odf_ca_project" static?

If not on Item #2, then, by far, your best method of achieving your objective is to build a function. And even if the number of rows is static, your best bet is a user-defined function such as

create or replace function get_dsti_proj_ref return varchar2 is
    hold_result varchar2(4000);
begin
    for r in (SELECT dsti_proj_ref from odf_ca_project) loop
        hold_result := hold_result||r.dsti_proj_ref;
    end loop;
    return hold_result;
end;
/

Function created.

Then, you can invoke that function to achieve your results:

SELECT get_dsti_proj_ref from dual;

GET_DSTI_PROJ_REF
-----------------------------------
Proj123Proj345Proj456Proj567Proj678

Let us know if this resolves your need.




Mufasa
(aka Dave of Sandy, Utah, USA)
[

http://www.dasages.com:

Providing low-cost remote Database Admin services]
Click here to join Utah Oracle Users Group on Tek-Tips if you use Oracle in Utah USA.