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

1
votes

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
3
oracleselect-into
If you post code, XML or data samples, please highlight those lines in the text editor and click on the "code samples" button ( { } ) on the editor toolbar to nicely format and syntax highlight it! – marc_s
possible duplicate of How can I combine multiple rows into a comma-delimited list in Oracle? – Ben

3 Answers

7
votes

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
0
votes

Maybe try DBMS_UTILITY.COMMA_TO_TABLE and TABLE_TO_COMMA to split/join csv:

DECLARE
  l_list1   VARCHAR2(50) := 'Tom,Dick,Harry,William';
  l_list2   VARCHAR2(50);
  l_tablen  BINARY_INTEGER;
  l_tab     DBMS_UTILITY.uncl_array;
BEGIN
  DBMS_OUTPUT.put_line('l_list1 : ' || l_list1);

  DBMS_UTILITY.comma_to_table (
     list   => l_list1,
     tablen => l_tablen,
     tab    => l_tab);

  FOR i IN 1 .. l_tablen LOOP
    DBMS_OUTPUT.put_line(i || ' : ' || l_tab(i));
  END LOOP;

  DBMS_UTILITY.table_to_comma (
     tab    => l_tab,
     tablen => l_tablen,
     list   => l_list2);

  DBMS_OUTPUT.put_line('l_list2 : ' || l_list2);
END;
-1
votes

You cannot insert multiple values into a single variable, unless you concatenate them somehow.

To get only a single value (not sure of the oracle syntax), 

select @myVar = select top 1 Field1 From myTable

Otherwise, to concatenate the values (again, not sure of Oracle)

set @myVar = ''  -- Get rid of NULL
select @myVar = @MyVar + ', ' +  Field1 From myTable