SQL multiple SELECT query with xmlagg function- Data not pulled in the required fashion
77,982
Solution 1
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
Solution 2
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
Author by
Raghu
Updated on August 20, 2020Comments
-
Raghu almost 4 years
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?
-
Raghu almost 12 yearsOne 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.
-
user1526671 almost 10 yearswhat does wm_concat do
-
cyberbit almost 8 yearsDo not use
WM_CONCAT
, it is undocumented and is removed from later versions of Oracle. -
not2savvy about 4 yearsFor a way to make sure that
dep_value
are aggregated in order, see this question.