如何在Oracle中使用COALESCE合并两行数据 [英] how to use COALESCE in oracle to combine data from two rows

问题描述

我有以下查询，该查询在循环内且正在中断

I have the following query which is inside a loop and is breaking

SELECT   COALESCE(v_user_grade || ', ', '') || user_grade
          INTO v_user_grade
          FROM EXPIRATION_HISTORY__2
           WHERE hist_id = v_test;

因此，基本上，在循环的每次迭代中，我们都会为v_test获得一个新值.在某些情况下，此查询将返回多行.那是当它破裂的时候.

So basically in each iteration of the loop we get a new value for v_test. In some cases this query would return multiple rows. that is when it is breaking.

我想要的是:

EXPIRATION_HISTORY__2中的数据:

Data in EXPIRATION_HISTORY__2:

hist_id    user_grade
301     ADMIN
302     ADMIN
302     USER

现在基于上述数据. ...在迭代的最后，其中v_test = 302.我希望v_user_grade包含(ADMIN, USER)

so now based on the above data. ...in the end of the iteration where v_test = 302. I would want v_user_grade to contain (ADMIN, USER)

推荐答案

我认为以下链接准确描述了您想要的内容:

I think the following link describes exactly that what you want:

http://www.oracle-base.com/articles/10g /StringAggregationTechniques.php

字符串聚合技术

有时需要汇总 从多行数据到一个 单行，给出数据列表 与特定值相关联.

On occasion it is necessary to aggregate data from a number of rows into a single row, giving a list of data associated with a specific value.

我不是Oracle专家；我不是Oracle专家.但是有多种解决方案，具体取决于您的数据库环境.

I'm not an Oracle expert; but there are multiple solutions, depending on your DB enviroment.

这篇关于如何在Oracle中使用COALESCE合并两行数据的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！