oracle 之 行转列函数

10g的wm_concat

11g的listagg，但是有4000字符的限制。

wm_concat用法举例

with tt as (select 'tom' as name, '24' as age from dual
union all
select 'tom','22' from dual
union all
select 'bob','11' from dual
union all
select 'bob','09' from dual)
select tt.name,to_char(wm_concat(tt.age)) from tt group by tt.name;

结果如下

listagg用法举例

with tt as (select 'tom' as name, '24' as age from dual
union all
select 'tom','22' from dual
union all
select 'bob','11' from dual
union all
select 'bob','09' from dual)
select tt.name,listagg(tt.age,',') within group(order by tt.age) as aa
from tt
group by tt.name;

结果如下

下方高能

用这两个函数的时候发现一个问题，就是如果被转换的列的类型是 NVARCHAR2，返回的结果就是空。

这个问题还没有解决，怀疑是字符集的问题，但是改为utf-8之后，还是有此情况。

有大神知晓其中原因的话，请留言，不胜感激