数据库列数是有限制的,如果行列转换到关系模型,需要知道行数,列数,排序,否则,可能每次结果都不一样,并且能转换的行数多不了,最大1012*1012的矩阵with tmp1(c1,c2) as (values('a',1),('b',2),('c',3)),tmp2(c1,c2) as (select listagg(c1,',') within group (order by c2),lista...
显示全部数据库列数是有限制的,如果行列转换到关系模型,需要知道行数,列数,排序,否则,可能每次结果都不一样,并且能转换的行数多不了,最大1012*1012的矩阵
with tmp1(c1,c2) as (values('a',1),('b',2),('c',3)),
tmp2(c1,c2) as (
select listagg(c1,',') within group (order by c2),listagg(c2,',') within group (order by c2) from tmp1 group by 1),
tmp3(c1) as (
select T.c4 from tmp2,lateral(values(tmp2.c1),(tmp2.c2)) as T(c4) group by T.c4 having count(*) >0)
select trim(substr(c1,1,instr(c1,',')-1)) ,trim(substr(c1,instr(c1,',',1,1) +1, instr(c1,',',1,2)-instr(c1,',',1,1)-1)) ,trim(substr(c1,instr(c1,',',1,2) +1, length(c1)-instr(c1,',',1,2))) from tmp3;
仅仅输出的话,转换结果行数多不了,因为最多列数有限制,listagg到varchar查询倒是可以快点,不过很容易行的长度超出,转成CLOB也多不了几行。
with tmp1(c1,c2) as (values('a',1),('b',2),('c',3)),
tmp2(c1,c2) as (
select listagg(c1,',') within group (order by c2),listagg(c2,',') within group (order by c2) from tmp1 group by 1)
select T.c3 from tmp2,lateral(values(tmp2.c1),(tmp2.c2)) as T(c3) group by T.c3 having count(*) >0;
xmlagg输出数据类型是XML,能装比较多的行。聚集成一行,然后按列输出,或者用syscat.columns数据拼union all,不会有很多的额外操作。
收起