Concatenating fields when BREAK ON command is used

Question

I have built a command that uses the BREAK ON command to stop the output of duplicate field names. For example:

f.name | f.value
f.name | f.value
f.name | f.value

becomes:

f.name | f.value
       | f.value
       | f.value

Is there any way to have this output as:

f.name | f.value,f.value,f.value

In some instances the f.name field with have over 20 f.values associated with it.

The output will eventually be used to import into other places so I am trying to make the output as friendly as possible.

score 2 · Answer 1 · answered Mar 16 '13 at 13:28

You're not looking for a SQL*Plus command, you're looking for a string aggregation.

Assuming your current query is:

select name, value from my_table

you can change it as follows to get your desired result. The DISTINCT is included to eliminate duplicate results in your list.

select name, listagg(value, ', ') within group (order by value) as value
  from ( select distinct name, value from my_table )
 group by name

LISTAGG() was only released in 11.2, if you're using an earlier version of Oracle you could use the undocumented function WM_CONCAT() or the user defined function STRAGG() as outlined in this useful page on string aggregation techniques.

Concatenating fields when BREAK ON command is used

1 Answers1