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:

Answers


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.


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'), ', ')...

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.


Need Your Help

MVC 5 - More than one entry point?

c# asp.net-mvc asp.net-mvc-5 owin asp.net-mvc-5.1

The standard Mvc web application template in VS 2013 has:

Android: Reading the HTML from a website and posting it to the screen

java android html parsing sdk

I'm aiming to make a very basic application which reads the HTML and stores it into a string. I'm only interested in one line from the website's source. I found a topic which suggested this: