乘法表

结果：

--------------------------------------------------------------------------------
1*1=1
2*1=2  2*2=4
3*1=3  3*2=6  3*3=9
4*1=4  4*2=8  4*3=12  4*4=16
5*1=5  5*2=10  5*3=15  5*4=20  5*5=25
6*1=6  6*2=12  6*3=18  6*4=24  6*5=30  6*6=36
7*1=7  7*2=14  7*3=21  7*4=28  7*5=35  7*6=42  7*7=49
8*1=8  8*2=16  8*3=24  8*4=32  8*5=40  8*6=48  8*7=56  8*8=64
9*1=9  9*2=18  9*3=27  9*4=36  9*5=45  9*6=54  9*7=63  9*8=72  9*9=81

1 、case when or decode（多列转一行）

with t1 as
 (select level x from dual connect by level <= 9),
t2 as
 (select a.x x, b.x y from t1 a, t1 b where a.x >= b.x)
select max(decode(y, 1, x || '*' || y || '=' || x * y)) col1,
       max(decode(y, 2, x || '*' || y || '=' || x * y)) col2,
       max(decode(y, 3, x || '*' || y || '=' || x * y)) col3,
       max(decode(y, 4, x || '*' || y || '=' || x * y)) col4,
       max(decode(y, 5, x || '*' || y || '=' || x * y)) col5,
       max(decode(y, 6, x || '*' || y || '=' || x * y)) col6,
       max(decode(y, 7, x || '*' || y || '=' || x * y)) col7,
       max(decode(y, 8, x || '*' || y || '=' || x * y)) col8,
       max(decode(y, 9, x || '*' || y || '=' || x * y)) col9
  from t2
 group by x
 order by x

2、listagg 函数（多列转一行）

with t1 as
 (select level x from dual connect by level <= 9),
t2 as
 (select a.x x, b.x y from t1 a, t1 b where a.x >= b.x)
select listagg(x || '*' || y || '='|| x * y, '  ') within group(order by x)
  from t2
 group by x;

3、wm_concat()函数（多列转一行）

with t1 as
 (select level x from dual connect by level <= 9),
t2 as
 (select a.x x, b.x y from t1 a, t1 b where a.x >= b.x)
          select to_char(wm_concat(x || '*' || y || '=' || x * y || ' ')
                         keep(dense_rank first order by x))
--keep取分组第一条数据，支持排序
            from t2
           group by x

4、sys_connect _by_path()函数

with t1 as
 (select level x from dual connect by level <= 9),
t2 as
 (select a.x x, b.x y from t1 a, t1 b where a.x >= b.x)
select substr(max(mup),2) from (select sys_connect_by_path(mut,',')mup
  from (select mut,
               rn,
               lag(rn) over(partition by substr(mut, 1, 1) order by rn) seq
          from (select x || '*' || y || '=' || x * y mut,
                       row_number() over(order by x, y) rn
                  from t2))
 start with seq is null
connect by prior rn = seq) group by substr(mup,2,1) order by substr(mup,2,1);