recursive query

[DT]

Hi

The problem...
To concatenate 'n' rows from a table in order and return as one row.

The table...
for testing, I have set up a table and populated as follows..
Name Null? Type
------------- -------- -----------
LINE_NO NUMBER(2)
TEXT VARCHAR2(5)
PKEY NUMBER

SQL> select * from t2;
LINE_NO TEXT PKEY
---------- ----- ----------
1 red 1
2 lorry 1
4 lorry 1
3 blue 1

The amount of lines can be 1 - nnn. The data held in the actual table is text split into 80 character chunks. This cannot be changed as it is part of a purchased package.

What I need is 1 row containing 'redlorrybluelorry'

I am running the following query and am sort of there.... Any takers ?

1 select
2 level
3 ,a.line_no
4 ,prior a.text||a.text text
5 from t2 a
6 where pkey=1
7 start with a.line_no = 1
8 connect by prior a.line_no+1 = a.line_no
9* and a.pkey = a.pkey
SQL> /

LEVEL LINE_NO TEXT
---------- ---------- ----------
1 1 red
2 2 redlorry
3 3 lorryblue
4 4 bluelorry

Thanks
Darren

[mkumarnk]

Hi Darren,

I think u can use a pl/Sql block for this.Declare cursor fot that table and concatinate each row to a variable.

I understood in this way..am i correct ?

Manoj