是否存在将多行聚合为一行的 Oracle SQL 查询？

发布于 2024-07-27 01:04:08 字数 505 浏览 3 评论 0原文

我有一个如下所示的表：

A 1 
A 2 
B 1 
B 2

我想生成一个如下所示的结果集：

A 1 2 
B 1 2

是否有 SQL 语句可以执行此操作？我正在使用甲骨文。

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

萌︼了一个春 2024-08-03 01:04:09

在 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

请参阅我的博客中的这篇文章以获取说明：

Oracle 10g 中的“noreferrer">GROUP_CONCAT

In 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:

GROUP_CONCAT in Oracle 10g

回复收藏 0 原文

居里长安 2024-08-03 01:04:09

尝试类似的方法：

SELECT
    field1,
    RTRIM(REPLACE(REPLACE(XMLAgg(XMLElement("x", field2) ORDER BY field2), '<x>'), '</x>', ' ')) AS field2s
  FROM yourTable
  GROUP BY field1

自由地受到在此 Oracle 论坛中找到的答案的启发。

编辑：事实证明，该解决方案非常资源密集型，请求涉及 10⁵ 行之类的内容。我最终用自定义聚合函数替换了它，如约翰建议。

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.

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.

回复收藏 0 原文

七堇年 2024-08-03 01:04:09

如果你有 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;
/

因此，你可以这样做：

select col1, get_separated_value(col1) from table_name

在这里摆弄

如果您有 Oracle 11g，则可以使用 listagg :

SELECT 
    age,
    LISTAGG(name, ' ') WITHIN GROUP (ORDER BY name) "names"
FROM table_x
GROUP BY age

在这里查找 Listagg

If you have got 10g, then you have to go through the function below:

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 :

SELECT 
    age,
    LISTAGG(name, ' ') WITHIN GROUP (ORDER BY name) "names"
FROM table_x
GROUP BY age

Fiddle here for Listagg

回复收藏 0 原文

春花秋月 2024-08-03 01:04:09

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

只需复制/粘贴并使用它。适用于 9i。

回复收藏 0 原文

心房的律动 2024-08-03 01:04:09

SELECT a , COLLECT(b) FROM foo GROUP BY a

在 pl/sql 中使用时非常有用 - 可以转换为用户定义的集合。

SELECT a , COLLECT(b) FROM foo GROUP BY a

very useful when used in pl/sql - can be casted to a user defined collection.

回复收藏 0 原文

记忆で 2024-08-03 01:04:08

（警告 - WM_CONCAT 是一个不受支持的函数，已在版本 12c 中删除。除非您使用的是非常旧的数据库，否则应该避免使用此函数。您可能应该使用 < code>LISTAGG。）

这取决于您使用的 Oracle 版本。如果它支持 wm_concat() 函数，那么你可以简单地执行如下操作：

SELECT field1, wm_concat(field2) FROM YourTable GROUP BY field2;

wm_concat() 基本上就像 group_concat()。它可能没有被记录下来，所以启动 ye olde sqlplus 并查看它是否在那里。

如果它不在那里，那么您将需要自己实现等效的东西。您可以在字符串聚合页面在 oracle-base.com。

(WARNING - WM_CONCAT is an unsupported function that was removed in version 12c. Unless you're using a very old database, you should avoid this function. You should probably use LISTAGG instead.)

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:

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.

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.

回复收藏 0 原文