是否有一个Oracle SQL查询将多个行聚合为一行?(复制)

This question already has an answer here:

这个问题已经有了答案:

SQL Query to concatenate column values from multiple rows in Oracle 13 answers
SQL查询，从Oracle 13答案中的多个行连接列值

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语句可以做到这一点?我使用Oracle。

7 个解决方案

#1

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't there, then you'll want to implement something equivalent yourself. You can find some instructions on how to do this in the string aggregation page at oracle-base.com.

如果它不存在，那么您需要自己实现一些等价的东西。在oracle-base.com的字符串聚合页面上，您可以找到一些关于如何实现这一点的说明。

#2

Pretty old topic, but it could help others since Oracle improved in the mean time.

这是一个非常古老的话题，但是它可以帮助其他人，因为Oracle同时也在改进。

The LISTAGG function is what you are looking for (in 11g at least)

LISTAGG函数就是你要找的(至少是11g)

#3

In Oracle 10g+:

在Oracle 10 g +:

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:

在我的博客中可以看到这篇文章的解释:

GROUP_CONCAT in Oracle 10g
GROUP_CONCAT在Oracle 10 g

#4

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 very resources intensive with requests involving something like 10⁵ rows. I ended up replacing this by custom aggregate functions as suggested by John.

编辑:这个解决方案被证明是非常资源密集的，请求涉及大约105行。我最后用John建议的定制聚合函数来代替它。

#5

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

Fiddle here

小提琴在这里

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

Fiddle here for Listagg

在这里小提琴Listagg

#6

User defined aggregate functions: http://www.adp-gmbh.ch/ora/sql/user_def_agg.html

用户定义的聚合函数:http://www.adp-gmbh. ch/ora/sql/user_def_def_agg.html

Just copy/paste and use it. Works on 9i.

只需复制/粘贴并使用它。我9日工作。

#7

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中使用时非常有用——可以被强制转换为用户定义的集合。

#1