listagg数据为可用格式? [英] listagg data to useable format?

问题描述

这是我第一次使用LISTAGG函数，我很困惑.我可以很容易地选择数据，但是USERS列的字符之间都有空格，并且在尝试复制粘贴时，不会复制该列中的数据.我尝试了两种不同的IDE.我在做错什么吗?

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?

示例:

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;

收益:

推荐答案

您的firstname列似乎已定义为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 

...，我也无法从SQL Developer复制/粘贴用户值，但是它显示为空格，如您从SQL * Plus所见:

... 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

如文档所述，listagg()函数始终返回varchar2(或raw)，因此传递nvarchar2值会导致隐式转换，从而将结果丢弃.

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.

如果您坚持使用该数据类型的列，则可以将其强制转换为listagg调用内的varchar2:

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               

但是您可能根本不希望它成为nvarchar2.

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

这篇关于listagg数据为可用格式?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！