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