在 Oracle SQL 语句中构建逗号分隔的值列表
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/5822700/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
Building a comma-separated list of values in an Oracle SQL statement
提问by VBAHole
I'm trying to build a comma-separated list of values out of a field in Oracle.
我正在尝试从 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 INTOand it works but if I have more than one row I get the fetch error.
但是，当我尝试这样做时，我总是收到有关 FROM 关键字不是预期的错误。我可以使用SELECT INTO并且它可以工作，但是如果我有不止一行，我会收到获取错误。
Why can't I do as follows:
为什么我不能这样做：
SELECT myVar = Field1
FROM myTable
回答by Justin Cave
In Oracle, you would use one of the many string aggregation techniquescollected by Tim Hall on this page.
在 Oracle 中，您将使用Tim Hall 在此页面上收集的众多字符串聚合技术之一。
If you are using 11.2,
如果您使用的是 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
在早期版本中，我更喜欢使用用户定义的聚合函数方法（Tim 被称为string_agg）来做
SELECT string_agg( display )
INTO l_list
FROM TestTable
回答by tbone
Maybe try DBMS_UTILITY.COMMA_TO_TABLE and TABLE_TO_COMMA to split/join csv:
也许尝试 DBMS_UTILITY.COMMA_TO_TABLE 和 TABLE_TO_COMMA 来拆分/加入 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;
回答by datagod
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),
要仅获取单个值（不确定 oracle 语法），
select @myVar = select top 1 Field1 From myTable
Otherwise, to concatenate the values (again, not sure of Oracle)
否则，连接值（再次，不确定 Oracle）
set @myVar = '' -- Get rid of NULL select @myVar = @MyVar + ', ' + Field1 From myTable
