0

I have a list of 15 values that I would like to concatenate to a comma separated list. Some of the values are null (which can be skipped) and I would like the list not to end in a comma. I would like to make the list so that if there are null values in between the SQL does not produce a list of values that look like: , , ,

For example:

select val1 || ', ' || val2 || ', ' val3

etc...

Should display: val1, val2, val3, etc.

Is there a clean way to do this in Oracle without a big case statement?

AAA
  • 2,388
  • 9
  • 32
  • 47
  • Are the values from different columns in one row, or the same column in multiple rows? If the latter - look at `listagg` if you're using 11g, or there are common ways to do string aggregation in earlier versions. – Alex Poole Mar 03 '14 at 19:26
  • The values are from different columns in one row - unfortunately listagg won't work here – AAA Mar 03 '14 at 19:28
  • Then no, unless you pivot the columns, which seems like overkill, you're stuck with manually building the concatenation. It doesn't seem particularly complicated though - just more typing... – Alex Poole Mar 03 '14 at 19:35
  • 1
    @AAA See the later part of this [answer](http://stackoverflow.com/a/11454297/2899629). Not sure that it is good method, but still it is kind of it. – Yaroslav Shabalin Mar 03 '14 at 19:45

1 Answers1

0

You can use NVL to replace null with a string in results

select NVL(val1, 'NA')....
Amit
  • 19,780
  • 6
  • 46
  • 54