listagg data to useable format?

问题:

This is my first time working with the LISTAGG function and I'm confused. I can select the data easily enough, but the characters of the USERS column all have spaces in between them, and when trying to copypaste it, no data from that column is copied. I've tried with two different IDEs. Am I doing something wrong?

Example:

select course_id, listagg(firstname, ', ') within group (order by course_id) as users
    from (
      select distinct u.firstname, u.lastname, u.student_id, cm.course_id
      from course_users cu
      join users u on u.pk1 = cu.users_pk1
      join course_main cm on cm.pk1 = cu.crsmain_pk1
      and cm.course_id like '2015SP%'
      )
group by course_id;

Yields:

回答1:

Your firstname column seems to be defined as nvarchar2:

with t as (
  select '2015SP.BOS.PPB.556.A'as course_id,
    cast('Alissa' as nvarchar2(10)) as firstname
  from dual
  union all select '2015SP.BOS.PPB.556.A'as course_id,
    cast('Dorothea' as nvarchar2(10)) as firstname
  from dual
)
select course_id, listagg(firstname, ', ')
  within group (order by course_id) as users
from t
group by course_id;

COURSE_ID            USERS                         
-------------------- ------------------------------
2015SP.BOS.PPB.556.A 

... and I can't copy/paste the users values from SQL Developer either, but it displays with spaces, as you can see from SQL*Plus:

COURSE_ID            USERS
-------------------- ------------------------------
2015SP.BOS.PPB.556.A  A l i s s a,  D o r o t h e a

As the documentation says, the listagg() function always returns varchar2 (or raw), so passing in an nvarchar2 value causes an implicit conversion which is throwing out your results.

If you're stuck with your column being of that data type, you could cast it to varchar2 inside the listagg call:

column users format a30
with t as (
  select '2015SP.BOS.PPB.556.A'as course_id,
    cast('Alissa' as nvarchar2(10)) as firstname
  from dual
  union all select '2015SP.BOS.PPB.556.A'as course_id,
    cast('Dorothea' as nvarchar2(10)) as firstname
  from dual
)
select course_id, listagg(cast(firstname as varchar2(10)), ', ')
  within group (order by course_id) as users
from t
group by course_id;

COURSE_ID            USERS                         
-------------------- ------------------------------
2015SP.BOS.PPB.556.A Alissa, Dorothea               

But you probably don't really want it to be nvarchar2 at all.

回答2:

I had similar problem, it turned out that the problem was with encoding. I got this solved like this (change to another encoding if needed):

...listagg(convert(firstname, 'UTF8', 'AL16UTF16'), ', ')...

回答3:

Apparently it's a known (unresolved?) bug in 11. TO_CHAR() worked for me...

SELECT wiporderno, LISTAGG(TO_CHAR(medium), ',') WITHIN GROUP(ORDER BY wiporderno) AS jobclassification 

...where medium was the problematic column/data type.