CODEWITHSUNDEEP
sqloracle-database
lsk123
lsk123

Reputation: 21

Concatenate column string values using listagg

I need to concatenate values of the column content. I tried to concatenate them using listagg, but it doesn't work. I don't know why because it doesn't return errors. Probably exists a better option, than listtagg to concatenate them

I had three essential columns:

ID, Step_id, timestamp and content. Content is split when its length exceed certain length.

When content is divided then id, step_id is the same but timestamp is other.

For example:

 Id | step_id | timestamp | content|
  1 |       A |  15:21:21 | ABCDEFG|
  1 |       A |  15:21:22 |    HIJK|

I try to connect this in:

Id | step_id |     content |
 1 |       A | ABCDEFGHIJK |

I've tried to use row_number and join parts but it doesn't work and it is logical.

My code:

    With dane as 
    (
    select id,step_id,row_number() over (partition by id, step_id, order by timestamp) as rno, cast(content as varchar(15024)) as content from xya),
    dane1 as (select id, content as con1,step_id from dane where rno=1)
    dane2 as (select id, content as con2 ,step_id from dane where rno=2)
    dane3 as (select id, content as con3 ,step_id from dane where rno=3)
    dane4 as (select id, content as con4,step_id from dane where rno=4)
    select dane3. id, con1||con2||con3||con4, dane1.step_id from 
    dane1 left join dane 2 on dane1.id=dane2.id and dane1.step_id=dane2.step_id
    left join dane3 on dane3.id=dane2.id and dane3.step_id=dane2.step_id

Upvotes: 0

Views: 123

Answers (2)

lsk123
lsk123

Reputation: 21

I try IT earlier but its dosent work. Propably due to format data = clob (9750 char). On normal data its work but not this case. Answers from program when i try use to-char: the the data type, length, od value od argument 1 of to-char is invalid SQL code=171 SQLstate=42815 driver 3.53.95. I work on db2

When i run without to-char answers from program is attempt to use a function when the aplication compability settings is set for a previus level. SQLcode =4743 SQLstate =56038 driver 3.53.95

Upvotes: 0

Sayan Malakshinov
Sayan Malakshinov

Reputation: 8655

Try this one:

select id, step_id, listagg(content)within group(order by timestamp) content
from xya
group by id,step_id;

Simple example with test data:


with xya( Id , step_id , timestamp , content) as (
select   1 ,       'A' ,  '15:21:21' , 'ABCDEFG' from dual union all
select   1 ,       'A' ,  '15:21:22' ,    'HIJK' from dual
)
select id, step_id, listagg(content)within group(order by timestamp) content
from xya
group by id,step_id;

        ID STEP_ID CONTENT
---------- ------- ------------------------------
         1 A       ABCDEFGHIJK

Upvotes: 1

Related Questions