具有xmlagg函数的SQL多重SELECT查询-数据未按要求的方式提取 [英] SQL multiple SELECT query with xmlagg function- Data not pulled in the required fashion

问题描述

我在Oracle中的数据是这样的

My data in Oracle is something like this

NAME | DEP_VALUE | ID_DEP

Amy     1         AA1234
Bob     2         BB4321
Clara   1         CC5678
Clara   2         CC7890
John    1         JJ6543
John    2         JJ7865
John    3         JJ7654
Tom     1         TT0987
Tom     2         TT6541
Tom     3         TT4087
Tom     4         TT3409

我需要以这种方式提取数据

NAME  | DEP_VALUE |  ID_DEP

Amy     1         AA1234
Bob     2         BB4321
Clara   1;2       CC5678;CC7890
John    1;2;3     JJ6543;JJ7865;JJ7654
Tom     1;2;3;4   TT0987;TT6541;TT4087;TT3409

我的查询如下

SELECT name,
   Rtrim(Xmlagg (Xmlelement (e, dep_value
                                || ';')).extract  ( '//text()' ), ','),
   Rtrim(Xmlagg (Xmlelement (e, id_dep
                                || ';')).extract  ( '//text()' ), ',')
FROM   (SELECT emp_name,
           dep.dep_value,
           dep.id_dep
    FROM   emp
           inner join dep
                   ON emp.name = dep.name
    WHERE  id_name IN (SELECT name
                       FROM   altname
                       WHERE  id_emp IN (SELECT id_emp
                                         FROM   cnames
                                         WHERE  emp_lvl LIKE '%GGG%')))
    GROUP  BY name,
      dep_value  

显示的结果是

NAME  | DEP_VALUE |  ID_DEP

Amy     1;         AA1234;
Bob     2;         BB4321;
Clara   1;         CC5678;
Clara   2;         CC7890;
John    1;         JJ6543;
John    2;         JJ7865;
John    3;         JJ7654;
Tom     1;         TT0987;
Tom     2;         TT6541;
Tom     3;         TT4087;
Tom     4;         TT3409;

如何像第二张表一样提取数据?我的SQL查询有什么错误?

How can I pull the data as in the 2nd table? What is the error in my sql query?

推荐答案

听起来您想要GROUP BY name而不是GROUP BY name, dep_value

SELECT name,
   Rtrim(Xmlagg (Xmlelement (e, dep_value
                                || ';')).extract  ( '//text()' ), ';'),
   Rtrim(Xmlagg (Xmlelement (e, id_dep
                                || ';')).extract  ( '//text()' ), ';')
FROM   (SELECT emp_name,
           dep.dep_value,
           dep.id_dep
    FROM   emp
           inner join dep
                   ON emp.name = dep.name
    WHERE  id_name IN (SELECT name
                       FROM   altname
                       WHERE  id_emp IN (SELECT id_emp
                                         FROM   cnames
                                         WHERE  emp_lvl LIKE '%GGG%')))
    GROUP  BY name

这篇关于具有xmlagg函数的SQL多重SELECT查询-数据未按要求的方式提取的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！