Oracle分组后拼接未分组字段

Oracle分组后拼接未分组字段

干货：
listagg( 要拼接的字段名 , 分隔符 ) within group (order by 排序的字段名 )

语句

select 分组字段名 as name,
       listagg(要拼接的字段名, ',') within group(order by 排序的字段名),
       listagg(要拼接的字段名, ',') within group(order by 排序的字段名2)
  from 表名
 group by 分组字段名;

ECharts 折线图中使用的语句（解决长度不一致导致数据错乱）

先分组获得等长的数据，然后再分组拼接

select s.ownerofficename as name,
       listagg(to_char(s.statisticsdate, 'yyyy-mm-dd'), ',') within group(order by s.statisticsdate) as xAxis,
       listagg(s.sumnumber, ',') within group(order by s.statisticsdate) as data
  from (select t.ownerofficenumber as ownerofficenumber,
               t.ownerofficename as ownerofficename,
               t.statisticsdate as statisticsdate,
               sum(t.sumnumber) as sumnumber
          from etc_handset_statistics_info t
         group by t.ownerofficenumber, t.ownerofficename, t.statisticsdate) s
 group by s.ownerofficenumber, s.ownerofficename
 order by s.OWNEROFFICENUMBER