0

I'm currently trying to create a report using SQL Developer.

I have these 2 tables:

PERSONS (IDPerson, NamePerson) PENALTIES (IDPenalty, DatePenalty, Description, IDPerson)

The tables are polulated.

How could I create a table like

THIS

using recursive queries in SQL? or it's there any other solution? Thank you in advance.

Bob Jarvis - Слава Україні
  • 48,992
  • 9
  • 77
  • 110
Mr.Gigi
  • 25
  • 8
  • 1
    I'm tempted to flag this as a duplicate of [this question](http://stackoverflow.com/questions/4686543/sql-query-to-concatenate-column-values-from-multiple-rows-in-oracle). – Bob Jarvis - Слава Україні Oct 10 '16 at 11:59
  • Agreed - an almost exact duplicate. The other question didn't need the join to retrieve the "name" associated with the id, but that is a separate and different kind of question (vs. the list aggregation question which is the main topic). –  Oct 10 '16 at 12:04

1 Answers1

2
select   p.nameperson as name, p.idperson as id,
         listagg(to_date(x.datepenalty, 'dd/mm/yyyy') || ' - ' || x.description, '; ')
                                      within group (order by x.datepenalty) as penalties
from     persons p left outer join penalties x
                   on p.idperson = x.idperson
group by p.idperson;

(Not tested - you didn't provide test data.)