按一个正常的查询语句,可能是这样的
select A,B,C from tb
-------------------------------
A B C
----------
a1 b1 c1
a2 b2 c2
a3 b3 c3
...
想把查询结果按行列值的方式插到结果表里,如
行 列 值
------------
1 1 a1
1 2 b1
1 3 c1
2 1 a2
2 2 b2
...请问大家有没有好的思路
select A,B,C from tb
-------------------------------
A B C
----------
a1 b1 c1
a2 b2 c2
a3 b3 c3
...
想把查询结果按行列值的方式插到结果表里,如
行 列 值
------------
1 1 a1
1 2 b1
1 3 c1
2 1 a2
2 2 b2
...请问大家有没有好的思路
select A,B,C,rownum as rn from tb )
union
select rn,2,b from (
select A,B,C,rownum as rn from tb )
union
select rn,3,c from (
select A,B,C,rownum as rn from tb )
SQL> with tb as(
2 select 'a1' a,'b1' b,'c1' c from dual union all
3 select 'a2','b2','c2' from dual union all
4 select 'a3','b3','c3' from dual)
5 select rn,1,a from (select A,B,C,rownum as rn from tb )
6 union
7 select rn,2,b from (select A,B,C,rownum as rn from tb )
8 union
9 select rn,3,c from (select A,B,C,rownum as rn from tb )
10 / RN 1 A
---------- ---------- --
1 1 a1
1 2 b1
1 3 c1
2 1 a2
2 2 b2
2 3 c2
3 1 a3
3 2 b3
3 3 c39 rows selected
with t as(
select 'a1' a,'b1' b,'c1' c from dual
union all
select 'a2','b2','c2' from dual
union all
select 'a3','b3','c3' from dual
)select 1 行,rownum 列,a 值 from t
union
select 2,rownum,b from t
union
select 3,rownum,c from t行 列 值
1 1 a1
1 2 a2
1 3 a3
2 1 b1
2 2 b2
2 3 b3
3 1 c1
3 2 c2
3 3 c3
with t as(
select 'a1' a,'b1' b,'c1' c from dual
union all
select 'a2','b2','c2' from dual
union all
select 'a3','b3','c3' from dual
)select 1 行,rownum 列,a 值 from t
union
select 2,rownum,b from t
union
select 3,rownum,c from t行 列 值
1 1 a1
1 2 a2
1 3 a3
2 1 b1
2 2 b2
2 3 b3
3 1 c1
3 2 c2
3 3 c3
with t as(
select 'a1' a,'b1' b,'c1' c from dual
union all
select 'a2','b2','c2' from dual
union all
select 'a3','b3','c3' from dual
)select rownum 行,1 列,a 值 from t
union
select rownum,2,b from t
union
select rownum,3,c from t
行 列 值
1 1 a1
1 2 b1
1 3 c1
2 1 a2
2 2 b2
2 3 c2
3 1 a3
3 2 b3
3 3 c3
上面的几位都是在语句已知,把三列都枚举出来达到目的的。
如果是未知语句,就需要识别语句中有多少列,再给点建议?
是否要用到游标
用动态游标的方式
先用DBMS_SQL.PARSE,获得列数
然后在游标FETCH过程中按列号取值,行号累加,写入结果表Anyway,分都给你们吧