回9楼:
10G的版本:
CREATE TABLE t1 (col_a VARCHAR2(10));
INSERT INTO t1 VALUES ('attr1');
INSERT INTO t1 VALUES ('attr3');
INSERT INTO t1 VALUES ('attr5');
CREATE TABLE t2 (col_b VARCHAR2(10),col_c VARCHAR2(10));
INSERT INTO t2 VALUES ('attr1','aaa');
INSERT INTO t2 VALUES ('attr1','bbb');
INSERT INTO t2 VALUES ('attr2','bbb');
INSERT INTO t2 VALUES ('attr3','ccc');
INSERT INTO t2 VALUES ('attr3','ddd');
INSERT INTO t2 VALUES ('attr4','ddd');
INSERT INTO t2 VALUES ('attr5','eee');
INSERT INTO t2 VALUES ('attr5','fff');
INSERT INTO t2 VALUES ('attr1','ggg'); -- 让attr3形成空档增加点难度
INSERT INTO t2 VALUES ('attr5','hhh');
COMMIT;
with vw_1 AS (SELECT col_b
, col_c
, row_number() over (partition by col_b order by col_c) as rn
, dense_rank() OVER (ORDER BY col_b) as rn_2
FROM t1,t2
WHERE t1.col_a = t2.col_b
)
,vw_driving as (select level l from dual
connect by level<=(select max(rn) from vw_1)
)
,vw_tmp AS (SELECT vw_1.col_b,vw_1.col_c,vw_1.rn_2, l
FROM vw_driving
LEFT JOIN vw_1
PARTITION BY (vw_1.rn_2)
ON l=rn
)
SELECT SYS_CONNECT_BY_PATH(col_b,'
') AS RESULT -- 两个引号之间打一个 TAB, 因为ORACLE不让用CHR(9)
FROM (SELECT DISTINCT col_b, rn_2 FROM vw_1)
WHERE connect_by_isleaf=1
CONNECT BY PRIOR rn_2+1=rn_2
START WITH rn_2=1
UNION ALL
SELECT SYS_CONNECT_BY_PATH(NVL(col_c,'*'),'
') AS RESULT-- 没有的值用 * 填充，也可用空格。两个引号之间打一个 TAB
FROM vw_tmp
WHERE connect_by_isleaf=1
CONNECT BY PRIOR L=L AND PRIOR rn_2+1=rn_2
START WITH rn_2=1;
RESULT
--------------------------------------
attr1 attr3 attr5
aaa ccc eee
bbb ddd fff
ggg * hhh |
