Building a comma-separated list of values in an Oracle SQL statement

oracle select-into
VBAHole picture VBAHole · Apr 28, 2011 · Viewed 18.4k times · Source

I'm trying to build a comma-separated list of values out of a field in Oracle.

I find some sample code that does this:

DECLARE @List VARCHAR(5000)
SELECT @List = COALESCE(@List + ', ' + Display, Display)
FROM TestTable
Order By Display

But when I try that I always get an error about the FROM keyword not being were it was expected. I can use SELECT INTO and it works but if I have more than one row I get the fetch error.

Why can't I do as follows:

SELECT myVar = Field1
FROM myTable

Answer

Justin Cave picture Justin Cave · Apr 28, 2011

In Oracle, you would use one of the many string aggregation techniques collected by Tim Hall on this page.

If you are using 11.2,

SELECT LISTAGG(display, ',') WITHIN GROUP (ORDER BY display) AS employees
  INTO l_list
  FROM TestTable

In earlier versions, my preference would be to use the user-defined aggregate function approach (Tim's is called string_agg) to do

SELECT string_agg( display )
  INTO l_list
  FROM TestTable

Related questions

Select multiple columns into multiple variables

How can I do in one select with multiple columns and put each column in a variable? Something like this: --code here V_DATE1 T1.DATE1%TYPE; V_DATE2 T1.DATE2%TYPE; V_DATE3 T1.DATE3%TYPE; SELECT T1.DATE1 …

Read More
How to store selection result in to variable in Oracle procedure

I write a simple procedure. I try to store selection result in variable. I use "SELECT INTO" query but I can not doing this. Example: DECLARE v_employeeRecord employee%ROWTYPE; BEGIN SELECT * INTO v_employeeRecord FROM Employee WHERE Salary > 10; …

Read More
How to select the value of a variable in Oracle?

I'm new to Oracle. How can I set this variable and show its value? declare nextId number; begin select HIBERNATE_SEQUENCE.nextval into nextId from dual; select nextId from dual; end; It complains that an INTO clause is expected in …

Read More