Oracle listagg 函数

   listagg 是Oracle的列转行的函数。但是此处的“列转行”并不准确。它确实可以把多列的数据转为一行显示。它是和sum类似的聚集函数。常用的方式如下：

 LISTAGG(XXX,XXX) WITHIN GROUP( ORDER BY XXX)

下面我们使用此函数生成乘法小九九:

setp 1： 生成1到9数字:

 select Level as col from dual connect by level <= 9

setp2： 计算1到9之间的乘法结果

with x as
 (select Level as col from dual connect by level <= 9)
select a.col as col_a,
         b.col as col_b,
         to_char(a.col) || 'X' || to_char(b.col) || '=' ||
         rpad(to_char(a.col * b.col), 2, ' ') as res
    from x a, x b    where b.col <= a.col

setp 3 按照 col_a 分组，同一组的res 放到一行：

with x as
 (select Level as col from dual connect by level <= 9),
y as
 (select a.col as col_a,
         b.col as col_b,
         to_char(a.col) || 'X' || to_char(b.col) || '=' ||
         rpad(to_char(a.col * b.col), 2, ' ') as res
    from x a, x b
     where b.col <= a.col)
select y.col_a,
       listagg(y.res, '  ') within group(order by y.col_b) as 小九九
  from y
 group by y.col_a