1. 测试数据 创建用 序列
select to_date('2009-01-01','yyyy-mm-dd'),'b','c','d',ROWNUM from dual connect by rownum<=5000000;
2. 用小表循环
3. 多行变一列
with atest as
(select to_date('2009-01-01','yyyy-mm-dd'),'b','c','d',ROWNUM as rn from dual connect by rownum<=10)
SELECT SUBSTR(MAX(SYS_CONNECT_BY_PATH(colname,':')),2) NEW_COL FROM
(
SELECT 'B'as colname, RN FROM atest WHERE rn<5
)
START WITH RN=1
CONNECT BY ROWNUM=RN;