SQL multiple SELECT query with xmlagg function- Data not pulled in the required fashion

Question

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

I need the data to be pulled in this fashion

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

My query is as follows

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

The result that is displayed is

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;

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

Justin Cave · Accepted Answer · 2012-06-19T19:21:41.050

6

It sounds like you want to GROUP BY name rather than 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

edited Jun 19 '12 at 19:21

answered Jun 19 '12 at 16:41

Justin Cave

227,342
24
367
384

2

One more thing. In the query above the portion in "extract( '//text()' ), ',')" should actually be "extract( '//text()' ), ';')".Otherwise the id_dep will look like JJ6543;JJ7865;JJ7654; instead of JJ6543;JJ7865;JJ7654. There will be an extra ";" in the result. – Raghu Jun 19 '12 at 18:57
For a way to make sure that `dep_value` are aggregated in order, see [this question](https://stackoverflow.com/questions/23600501/how-to-order-the-fields-getting-aggregated-by-xmlagg-function/23600653). – not2savvy Apr 07 '20 at 14:00

score 4 · Answer 2 · answered Aug 19 '14 at 21:41

Just to provide further explanation on xmlagg, and add another option with Oracle 11g.

http://www.dba-oracle.com/t_display_multiple_column_values_same_rows.htm

select
  deptno,
  listagg (ename, ',') 
WITHIN GROUP 
(ORDER BY ename) enames
FROM 
  emp
GROUP BY 
   deptno
/

Output:

DEPTNO ENAMES                                            
---------- --------------------------------------------------
    10 CLARK,KING,MILLER                                 
    20 ADAMS,FORD,JONES,SCOTT,SMITH                
    30 ALLEN,BLAKE,JAMES,MARTIN,TURNER,WARD

score 0 · Answer 3 · edited Apr 22 '14 at 15:41

0

Try like this more simple

select NAME,replace(wm_concat(DEP_VALUE),',',';') as DEP_VALUE, replace(wm_concat(ID_DEP),',',';') as ID_DEP from yourtable 
where dep_value<2000 group by NAME

note: you need to limit your dep_value length and you can make your assumption, because you cannot get your string result to long, hope this is help your job

edited Apr 22 '14 at 15:41

demongolem

9,474
36
90
105

answered Apr 22 '14 at 15:22

rahmad76

1

what does wm_concat do – user1526671 Jun 19 '14 at 12:34
2

Do not use `WM_CONCAT`, it is undocumented and is removed from later versions of Oracle. – cyberbit Jun 27 '16 at 17:05

SQL multiple SELECT query with xmlagg function- Data not pulled in the required fashion

3 Answers3

Linked