是否有将多行聚合为一行的 Oracle SQL 查询？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/1120706/
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
Is there an Oracle SQL query that aggregates multiple rows into one row?
提问by user128807
I have a table that looks like this:
我有一张看起来像这样的表：
A 1
A 2
B 1
B 2
And I want to produce a result set that looks like this:
我想生成一个如下所示的结果集：
A 1 2
B 1 2
Is there a SQL statement that will do this? I am using Oracle.
是否有执行此操作的 SQL 语句？我正在使用甲骨文。
Related questions:
相关问题：
- Returning multiple rows from a single rowMy question is close to the opposite of this question.
- Use LINQ to concatenateThis is exactly what I want to do, but without LINQ.
回答by John Hyland
It depends on the version of Oracle you're using. If it supports the wm_concat() function, then you can simply do something like this:
这取决于您使用的 Oracle 版本。如果它支持 wm_concat() 函数，那么您可以简单地执行以下操作：
SELECT field1, wm_concat(field2) FROM YourTable GROUP BY field2;
wm_concat() basically works just like group_concat()in MySQL. It may not be documented, so fire up ye olde sqlplus and see if it's there.
wm_concat() 基本上就像MySQL 中的group_concat()一样。它可能没有记录在案，所以启动 ye olde sqlplus 看看它是否在那里。
If it isn'tthere, then you'll want to implement something equivalent yourself. You can find some instructions on how to do this in the string aggregation pageat oracle-base.com.
如果它不存在，那么您将需要自己实现一些等效的东西。您可以在 oracle-base.com的字符串聚合页面中找到有关如何执行此操作的一些说明。
回答by user1973071
回答by Quassnoi
In Oracle 10g+:
在Oracle 10g+：
SELECT *
FROM (
SELECT *
FROM mytable
MODEL
PARTITION BY
(grouper)
DIMENSION BY
(ROW_NUMBER() OVER (PARTITION BY grouper ORDER BY id) AS rn)
MEASURES
(val, val AS group_concat, 0 AS mark)
RULES SEQUENTIAL ORDER (
group_concat[rn > 1] ORDER BY rn = group_concat[CV() - 1] || ', ' || val[CV()],
mark[ANY] ORDER BY rn = PRESENTV(mark[CV() + 1], 0, 1)
)
)
WHERE mark = 1
ORDER BY
grouper
See this article in my blog for explanations:
有关解释，请参阅我博客中的这篇文章：
回答by Mac
Try something like :
尝试类似：
SELECT
field1,
RTRIM(REPLACE(REPLACE(XMLAgg(XMLElement("x", field2) ORDER BY field2), '<x>'), '</x>', ' ')) AS field2s
FROM yourTable
GROUP BY field1
Freely inspired by an answer found in this Oracle forum.
受到此 Oracle 论坛中的答案的自由启发。
EDIT:this solution proved veryresources intensive with requests involving something like 105rows. I ended up replacing this by custom aggregate functions as suggested by John.
编辑：这个解决方案被证明是非常资源密集型的，请求涉及 10 5行。我最终按照John 的建议用自定义聚合函数替换了它。
回答by hsuk
If you have got 10g, then you have to go through the function below:
如果您有10g，那么您必须通过以下功能：
CREATE OR REPLACE FUNCTION get_separated_value (input_val in number)
RETURN VARCHAR2
IS
return_text VARCHAR2(10000) := NULL;
BEGIN
FOR x IN (SELECT col2 FROM table_name WHERE col1 = input_val) LOOP
return_text := return_text || ' ' || x.col2 ;
END LOOP;
RETURN return_text;
END;
/
So, you can do like:
所以，你可以这样做：
select col1, get_separated_value(col1) from table_name
If you have got oracle 11g, you can use listagg:
如果你有 oracle 11g，你可以使用listagg：
SELECT
age,
LISTAGG(name, ' ') WITHIN GROUP (ORDER BY name) "names"
FROM table_x
GROUP BY age
回答by haki
SELECT a , COLLECT(b) FROM foo GROUP BY a
very useful when used in pl/sql - can be casted to a user defined collection.
在 pl/sql 中使用时非常有用 - 可以转换为用户定义的集合。
回答by jva
User defined aggregate functions: http://www.adp-gmbh.ch/ora/sql/user_def_agg.html
用户定义的聚合函数：http: //www.adp-gmbh.ch/ora/sql/user_def_agg.html
Just copy/paste and use it. Works on 9i.
只需复制/粘贴并使用它。适用于 9i。
