在 Oracle SQL 语句中构建逗号分隔的值列表
我正在尝试从 Oracle 中的字段构建一个以逗号分隔的值列表。
我找到了一些执行此操作的示例代码:
DECLARE @List VARCHAR(5000)
SELECT @List = COALESCE(@List + ', ' + Display, Display)
FROM TestTable
Order By Display
但是当我尝试这样做时,我总是收到有关 FROM 关键字不是预期的错误。我可以使用 SELECT INTO ,它可以工作,但如果我有多于一行,我会收到获取错误。
为什么我不能执行以下操作:
SELECT myVar = Field1
FROM myTable
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
如果你对这篇内容有疑问,欢迎到本站社区发帖提问 参与讨论,获取更多帮助,或者扫码二维码加入 Web 技术交流群。
绑定邮箱获取回复消息
由于您还没有绑定你的真实邮箱,如果其他用户或者作者回复了您的评论,将不能在第一时间通知您!
发布评论
评论(3)
在 Oracle 中,您可以使用由以下组织收集的众多字符串聚合技术之一蒂姆·霍尔在本页。
如果您使用的是 11.2,
在早期版本中,我的偏好是使用用户定义的聚合函数方法(Tim 的称为
string_agg
)来执行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,
In earlier versions, my preference would be to use the user-defined aggregate function approach (Tim's is called
string_agg
) to do也许尝试 DBMS_UTILITY.COMMA_TO_TABLE 和 TABLE_TO_COMMA 来拆分/连接 csv:
Maybe try DBMS_UTILITY.COMMA_TO_TABLE and TABLE_TO_COMMA to split/join csv:
不能将多个值插入到单个变量中,除非以某种方式将它们连接起来。
仅获取单个值(不确定 Oracle 语法),
否则,连接这些值(同样,不确定 Oracle)
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),
Otherwise, to concatenate the values (again, not sure of Oracle)