在Oracle SQL语句中以逗号分隔的值列表 [英] Building a comma-separated list of values in an Oracle SQL statement

问题描述

我正在尝试从Oracle的字段中构建一个逗号分隔的值列表.

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

但是，当我尝试执行该操作时，总是会收到我认为不是FROM关键字的错误.我可以使用SELECT INTO并起作用，但是如果我有多行，则会出现提取错误.

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

推荐答案

在Oracle中，您将使用许多

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

如果您使用的是11.2，

If you are using 11.2,

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

在早期版本中，我更喜欢使用用户定义的聚合函数方法(Tim被称为string_agg)来完成

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

这篇关于在Oracle SQL语句中以逗号分隔的值列表的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！