CODEWITHSUNDEEP
sqloracle-databasejoingroup-by
Jimenemex
Jimenemex

Reputation: 3166

Oracle - concat one or more rows to different result columns

I have a table structure with data which looks like:

EventNbr | NoteNbr | NoteText
1         1        Example title
1         2        text1
1         3        text2
2         4        Example title 2
3         5        Example title 3
3         6        text3

What I need as a result is a data set which looks like

EventNbr | Title         | Notes
1         Example Title    text1,text2
2         Example Title2
3         Example Title3   text3

I am basically taking the minimum NoteNbr from each EventNbr and putting it in the Title column and then every other NoteNbr after the MIN would be the concatenated together with a comma in the Notes column.

What I currently have works, but only for EventNbrs which have multiple NoteNbr rows. It does not work for items which only have one NoteNbr row like EventNbr 2 above.

SELECT A.EventNbr,
       MIN(A.NoteText) AS Title,
       LISTAGG(A.NoteText, ',') WITHIN GROUP(ORDER BY A.NoteNbr) AS Notes
  FROM EventNote A
 INNER JOIN (SELECT Min(NoteNbr) Min_NoteNbr, EventNbr
               FROM EventNote
              GROUP BY EventNbr) B
    ON (A.NoteNbr <> B.Min_NoteNbr AND A.EventNbr = B.EventNbr)
 INNER JOIN EventNote C
    ON (C.NoteNbr = B.Min_NoteNbr AND C.EventNbr = B.EventNbr)
 GROUP BY A.EventNbr;

Result:

EventNbr | Title         | Notes
1         Example Title    text1,text2
3         Example Title3   text3

What do I need to add to consider scenarios where there is only one NoteNbr row?

Upvotes: 0

Views: 77

Answers (2)

user5683823
user5683823

Reputation:

It may be best to run the aggregation first. This will produce almost the result you need, except it will still concatenate the title at the beginning of the notes. That can be corrected after the fact, using standard string functions substr, instr and concatenation. (The latter is needed to deal with the "exceptional case" you mentioned, when there are no actual notes.)

The advantage is that the additional operation is only performed on the output - expected to be (far?) fewer rows than the input, and the additional operation is a trivial string manipulation rather than an additional layer of sorting and partitioning.

Something like this - assuming the inputs are all in a single table (as the first part of your question implies) rather than in different tables (as your existing code suggests). I included a with clause to simulate the input table.

Note - the execution plan shows that the optimizer is smart enough to merge the subquery into the main query; the plan consists of a single SELECT operation over an aggregation (GROUP BY). all_notes is replaced with its long definition as a listagg right in the subquery, and the outer query is completely eliminated. So we have the best of both worlds: a query that can be read, but the execution is as efficient as possible.

with
  eventnote(eventnbr, notenbr, notetext) as (
    select 1, 1, 'Example title'   from dual union all
    select 1, 2, 'text1'           from dual union all
    select 1, 3, 'text2'           from dual union all
    select 2, 4, 'Example title 2' from dual union all
    select 3, 5, 'Example title 3' from dual union all
    select 3, 6, 'text3'           from dual
  )
select eventnbr, title,
       substr(all_notes, instr(all_notes || ',', ',') + 1) as notes
from   (
         select eventnbr,
                min(notetext) keep (dense_rank first order by notenbr) as title,
                listagg(notetext, ',') within group (order by notenbr) as all_notes
         from   eventnote
         group  by eventnbr
       )
order  by eventnbr
;

EVENTNBR  TITLE            NOTES                         
--------  ---------------  ------------------------------
       1  Example title    text1,text2                   
       2  Example title 2                               
       3  Example title 3  text3

Upvotes: 1

Gordon Linoff
Gordon Linoff

Reputation: 1269873

You can use conditional aggregation and row_number():

select eventnbr,
       max(case when seqnum = 1 then notetext end) as title,
       listagg(case when seqnum > 1 then notetext end, ',') within  group (order by seqnum) as notes
from (select en.*,
             row_number() over (partition by eventnbr order by notenbr) as seqnum
      from eventnote en
     ) en
group by eventnbr;

Upvotes: 2

Related Questions