I am using SQL Developer 3.1.07 on an 11g database When I use listagg to extract multiple values from a field I get a space between each character in the results for the listagg column. The query returns all the values I expect to see, it's just the extra spaces that are driving me nuts. Any thoughts?
Here is one query that I have used, but it happens everytime I use listagg in a query:
select a.personnum Emp_ID
, a.personfullname Name
, a.companyhiredtm Hire_Date
, a.employmentstatus Status
, a.employmentstatusdt Status_Date
, h.Supervisor, h.Agency
from vp_employeev42 a
left outer join (select f.personid
, listagg (g.personcstmdatatxt, ',') within group
(order by g.customdatadefid) Supervisor
from vp_employeev42 f
left outer join personcstmdata g
on f.personid = g.personid
where f.personnum like 'T%'
and f.homelaborlevelnm3 = '1872'
and (g.customdatadefid = '1'
or g.personcstmdatatxt is null)
group by f.personid) h
on a.personid = h.personid
left outer join (select f.personid
, listagg (g.personcstmdatatxt, ',')
within group (order by g.customdatadefid) Agency
from vp_employeev42 f
left outer join personcstmdata g
on f.personid = g.personid
where f.personnum like 'T%'
and homelaborlevelnm3 = '1872'
and (g.customdatadefid = '3'
or g.personcstmdatatxt is null)
group by f.personid) h
on a.personid = h.personid
where personnum like 'T%'
and homelaborlevelnm3 = '1872'
order by personnum;
Here are the results I get:
EMP_ID,NAME,HIRE_DATE,STATUS,STATUS_DATE,SUPERVISOR,AGENCY
T98999,Lxxxxm, Lxxxn,20-SEP-12,Active,20-SEP-12,, S t a f f m a r k
T98989,Fxxxxn, Dxxxxa,10-DEC-12,Active,10-DEC-12,, S t a f f m a r k
T99989,Hxxxs, Cxxxxxa,02-OCT-12,Active,02-OCT-12,, S t a f f m a r k
T99999,Hxxxs, Dxxxn,30-JAN-12,Terminated,21-MAY-12, C x x x x x x x x x r T x x x x r, P R O L O G I S T I X
The Oracle TRIM function does not trim spaces between words. You can use a regular expression with REGEXP_REPLACE to remove occurrences of more than once space. Or, you can use the REPLACE function to remove all spaces between words – but this would result in a single long word.
The results of listagg are constrained to the max size of VARCHAR2(4000).
In order to concatenate field values, I would use “GROUP_CONCAT” function in Virtual DataPort Administration tool which is similar to LISTAGG function. For example, GROUP_CONCAT('<row separator>',<field_name>)
The Oracle LISTAGG() function is an aggregation function that transforms data from multiple rows into a single list of values separated by a specified delimiter.
are you using UTF-16
+ NVARCHAR2
by any chance? eg this:
SQL> select * from nls_database_parameters where parameter='NLS_NCHAR_CHARACTERSET';
PARAMETER VALUE
------------------------------ ----------------------------------------
NLS_NCHAR_CHARACTERSET AL16UTF16
SQL> drop table test;
Table dropped.
SQL> create table test(a nvarchar2(10));
Table created.
SQL> insert into test values ('test');
1 row created.
SQL> insert into test values ('test 2');
1 row created.
SQL> select listagg(a, ',') within group (order by 1) from test group by 1;
LISTAGG(A,',')WITHINGROUP(ORDERBY1)
--------------------------------------------------------------------------------
t e s t, t e s t 2
you could cast to a char to get round this. IF this is not acceptible, you need to raise a ticket with Oracle support.
SQL> select listagg(to_char(a),',') within group (order by 1) from test group by 1;
LISTAGG(TO_CHAR(A),',')WITHINGROUP(ORDERBY1)
--------------------------------------------------------------------------------
test,test 2
SQL>
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With