Oracle equivalent to SQL Server STUFF function?

[Originally posted by]:

Does Oracle have its own implementation of SQL Server stuff function?

Stuff allows you to receive one value from a multi row select. Consider my situation below

 1      A         Dave
 2      A         John
 3      B         Bob

I would like to write a select statement, but I want the PERSON names to be in a single row.

For example, when I select from this table, I want to achieve the following

A           Dave, John
B           Bob

I haven’t been able to find a simple solution so far, it may be a case of writing my own function to use, but I’m not entirely sure of how to approach this, any ideas?

The main business use of this, will be to have a select statement that shows each house, and against that house to have one column which lists everyone that lives in that house. The house ref in this select must be unique, hence needing to concatenate the persons



Oracle 11.2 includes a new function LISTAGG to do this.

Prior to that you could use Tom Kyte’s STRAGG function.


The “no add-ons/no undocumented functions” Oracle solution (prior to 11.2 as Tony mentions) is:

select c1, ltrim(sys_connect_by_path(c2,','),',') persons
   select c1, c2, 
    row_number() over (partition by c1 order by c2 ) rn
       select house_ref c1, person c2 
        from housetable 
  where connect_by_isleaf=1
  connect by prior rn+1 =rn and prior c1 = c1
  start with rn=1

What is the Oracle equivalent of SQL Server’s IsNull() function?

[Originally posted by]:

coalesce is supported in both Oracle and SQL Server and serves essentially the same function as nvl and isnull. (There are some important differences, coalesce can take an arbitrary number of arguments, and returns the first non-null one. The return type for isnull matches the type of the first argument, that is not true for coalesce, at least on SQL Server.)


Instead of ISNULL(), use NVL().


SELECT ISNULL(SomeNullableField, 'If null, this value') FROM SomeTable


SELECT NVL(SomeNullableField, 'If null, this value') FROM SomeTable