using cursor how can i display this on dbms_output.put_line?

Question

select distinct tbl2.id, tbl1.year
from table1 tbl1 join table2 tbl2
on(tbl1.tbl2id = tbl2.id)

here's the output of this query

ID     YEAR    
1      2001   
1      2002    
2      1995   
2      1996

but the output that I want is something like this

1 - 2001, 2002     
2 - 1995, 1996

Aleksej · Accepted Answer · 2018-02-01 06:30:45Z

3

Without using cursors and PLSQL, you can use a LISTAGG:

select id || ' - ' || listagg(year, ', ') within group (order by year)
from (
        yourQuery
      )
group by id

answered Feb 1, 2018 at 6:30

Aleksej

23.1k6 gold badges38 silver badges41 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

XING · Accepted Answer · 2018-02-01 06:40:33Z

2

You can do it as below. First you can use LISTAGG to get the data in a format separated by comma and then run a loop;

Declare

Cursor xyz is 
          select  tbl2.id,listagg(tbl1.year,',') within group (ORDER BY tbl1.id) 
         from table1 tbl1 
          join table2 tbl2
         on tbl1.id = tbl2.id
         group by tbl2.id 
         order by 1 ;

var1 number;    
var2 varchar2(100);

begin

 Open xyz;
 Loop
  fetch xyz into var1, var2;

  EXIT WHEN XYZ%NOTFOUND;

  DBMS_OUTPUT.PUT_LINE('ID-'|| var1 || 'YEAR-' ||var2);

  END Loop;

  close xyz;
End;

DEMO

edited Feb 1, 2018 at 6:40

answered Feb 1, 2018 at 6:34

XING

9,9264 gold badges25 silver badges40 bronze badges

Collectives™ on Stack Overflow

using cursor how can i display this on dbms_output.put_line?

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related