-2

I have a table with data like this:

ID, END_DATE,   CLOSE_DATE

1,  2018-05-18, 2018-05-15 

2,  2018-05-18, 2018-05-14

3,  2018-05-18, 2018-05-11

4,  2018-05-18, 2018-05-10

I need the output when i query in Oracle server 

 END_DATE    CLOSE_DATE                                    ID

 2018-05-18  [2018-05-15,2018-05-14,2018-05-11,2018-05-10] [1,2,3,4]

I have tried to use listagg, but its working either for Id or Close Date but not both.

select (listagg(CLOSE_DATE,',') within group (order by CLOSE_DATE DESC)) CLOSE_DATE
     , END_DATE
  from TBL_S_PLCLOSEDATE
 where D_END='18-MAY-2018'

Please let me know how to write the sql for the same in Oracle server.

collapsar
  • 17,010
  • 4
  • 35
  • 61
Vasanth
  • 75
  • 8

1 Answers1

1

Have you tried this?

select end_date,
       listagg(CLOSE_DATE, ',') within group (order by CLOSE_DATE DESC) as close_dates,
       listagg(id, ',') within group (order by id) as ids
from TBL_S_PLCLOSEDATE
where D_END = date '2018-05-18'
group by end_date;
Gordon Linoff
  • 1,242,037
  • 58
  • 646
  • 786