oracle行列转换总结

希望对大家有所帮助，同时有何错疏，恳请大家指出，

我也是在写作过程中学习，算是一起和大家学习吧。

在写作的过程中也参考了一些资料和网友的文章，在此表示感谢。

行列转换包括以下六种情况：

*列转行

*行转列

*多列转换成字符串

*多行转换成字符串

*字符串转换成多列

*字符串转换成多行

下面分别进行举例介绍。

首先声明一点，有些例子需要如下10g及以后才有的知识：

a。掌握model子句，

b。正则表达式

c。加强的层次查询

讨论的适用范围只包括8i,9i,10g及以后版本。begin：

view plaincopy to clipboardprint?

01.  

02.1、列转行   

03.CREATE TABLE t_col_row(   

04.ID INT,   

05.c1 VARCHAR2(10),   

06.c2 VARCHAR2(10),   

07.c3 VARCHAR2(10));   

08.  

09.INSERT INTO t_col_row VALUES (1, 'v11', 'v21', 'v31');   

10.INSERT INTO t_col_row VALUES (2, 'v12', 'v22', NULL);   

11.INSERT INTO t_col_row VALUES (3, 'v13', NULL, 'v33');   

12.INSERT INTO t_col_row VALUES (4, NULL, 'v24', 'v34');   

13.INSERT INTO t_col_row VALUES (5, 'v15', NULL, NULL);   

14.INSERT INTO t_col_row VALUES (6, NULL, NULL, 'v35');   

15.INSERT INTO t_col_row VALUES (7, NULL, NULL, NULL);   

16.COMMIT;   

17.  

18.SELECT * FROM t_col_row;   

19.  

20.1)UNION ALL  

21.适用范围：8i,9i,10g及以后版本   

22.SELECT id, 'c1' cn, c1 cv   

23.  FROM t_col_row   

24.UNION ALL  

25.SELECT id, 'c2' cn, c2 cv   

26.  FROM t_col_row   

27.UNION ALL  

28.SELECT id, 'c3' cn, c3 cv FROM t_col_row;   

29.  

30.若空行不需要转换，只需加一个where条件，   

31.WHERE COLUMN IS NOT NULL 即可。   

32.  

33.2)MODEL   

34.适用范围：10g及以后   

35.SELECT id, cn, cv FROM t_col_row   

36.MODEL   

37.RETURN UPDATED ROWS  

38.PARTITION BY (ID)   

39.DIMENSION BY (0 AS n)   

40.MEASURES ('xx' AS cn,'yyy' AS cv,c1,c2,c3)   

41.RULES UPSERT ALL  

42.(   

43.  cn[1] = 'c1',   

44.  cn[2] = 'c2',   

45.  cn[3] = 'c3',   

46.  cv[1] = c1[0],   

47.  cv[2] = c2[0],   

48.  cv[3] = c3[0]   

49.  )   

50.ORDER BY ID,cn;   

51.  

52.3)collection   

53.适用范围：8i,9i,10g及以后版本   

54.要创建一个对象和一个集合：   

55.CREATE TYPE cv_pair AS OBJECT(cn VARCHAR2(10),cv VARCHAR2(10));   

56.  

57.CREATE TYPE cv_varr AS VARRAY(8) OF cv_pair;   

58.  

59.SELECT id, t.cn AS cn, t.cv AS cv   

60.  FROM t_col_row,   

61.       TABLE(cv_varr(cv_pair('c1', t_col_row.c1),   

62.                     cv_pair('c2', t_col_row.c2),   

63.                     cv_pair('c3', t_col_row.c3))) t   

.ORDER BY 1, 2;   

65.  

66.2、行转列   

67.  

68.CREATE TABLE t_row_col AS  

69.SELECT id, 'c1' cn, c1 cv   

70.  FROM t_col_row   

71.UNION ALL  

72.SELECT id, 'c2' cn, c2 cv   

73.  FROM t_col_row   

74.UNION ALL  

75.SELECT id, 'c3' cn, c3 cv FROM t_col_row;   

76.  

77.SELECT * FROM t_row_col ORDER BY 1,2;   

78.  

79.1)AGGREGATE FUNCTION  

80.适用范围：8i,9i,10g及以后版本   

81.SELECT id,   

82.       MAX(decode(cn, 'c1', cv, NULL)) AS c1,   

83.       MAX(decode(cn, 'c2', cv, NULL)) AS c2,   

84.       MAX(decode(cn, 'c3', cv, NULL)) AS c3   

85.  FROM t_row_col   

86.GROUP BY id   

87.ORDER BY 1;   

88.  

.MAX聚集函数也可以用sum、min、avg等其他聚集函数替代。   

90.  

91.被指定的转置列只能有一列，但固定的列可以有多列，请看下面的例子：   

92.  

93.SELECT mgr, deptno, empno, ename FROM emp ORDER BY 1, 2;   

94.  

95.SELECT mgr,   

96.       deptno,   

97.       MAX(decode(empno, '7788', ename, NULL)) "7788",   

98.       MAX(decode(empno, '7902', ename, NULL)) "7902",   

99.       MAX(decode(empno, '7844', ename, NULL)) "7844",   

100.       MAX(decode(empno, '7521', ename, NULL)) "7521",   

101.       MAX(decode(empno, '7900', ename, NULL)) "7900",   

102.       MAX(decode(empno, '7499', ename, NULL)) "7499",   

103.       MAX(decode(empno, '7654', ename, NULL)) "7654"  

104.  FROM emp   

105.WHERE mgr IN (7566, 7698)   

106.   AND deptno IN (20, 30)   

107.GROUP BY mgr, deptno   

108.ORDER BY 1, 2;   

109.  

110.这里转置列为empno，固定列为mgr，deptno。   

111.  

112.还有一种行转列的方式，就是相同组中的行值变为单个列值，但转置的行值不变为列名：   

113.  

114.ID        CN_1        CV_1        CN_2        CV_2        CN_3        CV_3   

115.1                c1                v11                c2                v21                c3                v31   

116.2                c1                v12                c2                v22                c3   

117.3                c1                v13                c2                                        c3                v33   

118.4                c1                                        c2                v24                c3                v34   

119.5                c1                v15                c2                                        c3   

120.6                c1                                        c2                                        c3                v35   

121.7                c1                                        c2                                        c3   

122.  

123.这种情况可以用分析函数实现：   

124.  

125.SELECT id,   

126.       MAX(decode(rn, 1, cn, NULL)) cn_1,   

127.       MAX(decode(rn, 1, cv, NULL)) cv_1,   

128.       MAX(decode(rn, 2, cn, NULL)) cn_2,   

129.       MAX(decode(rn, 2, cv, NULL)) cv_2,   

130.       MAX(decode(rn, 3, cn, NULL)) cn_3,   

131.       MAX(decode(rn, 3, cv, NULL)) cv_3   

132.  FROM (SELECT id,   

133.               cn,   

134.               cv,   

135.               row_number() over(PARTITION BY id ORDER BY cn, cv) rn   

136.          FROM t_row_col)   

137.GROUP BY ID;   

138.  

139.2)PL/SQL   

140.适用范围：8i,9i,10g及以后版本   

141.这种对于行值不固定的情况可以使用。   

142.下面是我写的一个包，包中   

143.p_rows_column_real用于前述的第一种不限定列的转换；   

144.p_rows_column用于前述的第二种不限定列的转换。   

145.  

146.CREATE OR REPLACE PACKAGE pkg_dynamic_rows_column AS  

147.  TYPE refc IS REF CURSOR;   

148.  

149.  PROCEDURE p_print_sql(p_txt VARCHAR2);   

150.  

151.  FUNCTION f_split_str(p_str VARCHAR2, p_division VARCHAR2, p_seq INT)   

152.    RETURN VARCHAR2;   

153.  

154.  PROCEDURE p_rows_column(p_table      IN VARCHAR2,   

155.                          p_keep_cols  IN VARCHAR2,   

156.                          p_pivot_cols IN VARCHAR2,   

157.                          p_where      IN VARCHAR2 DEFAULT NULL,   

158.                          p_refc       IN OUT refc);   

159.  

160.  PROCEDURE p_rows_column_real(p_table     IN VARCHAR2,   

161.                               p_keep_cols IN VARCHAR2,   

162.                               p_pivot_col IN VARCHAR2,   

163.                               p_pivot_val IN VARCHAR2,   

1.                               p_where     IN VARCHAR2 DEFAULT NULL,   

165.                               p_refc      IN OUT refc);   

166.END;   

167./   

168.CREATE OR REPLACE PACKAGE BODY pkg_dynamic_rows_column AS  

169.  

170.  PROCEDURE p_print_sql(p_txt VARCHAR2) IS  

171.    v_len INT;   

172.  BEGIN  

173.    v_len := length(p_txt);   

174.    FOR i IN 1 .. v_len / 250 + 1 LOOP   

175.      dbms_output.put_line(substrb(p_txt, (i - 1) * 250 + 1, 250));   

176.    END LOOP;   

177.  END;   

178.  

179.  FUNCTION f_split_str(p_str VARCHAR2, p_division VARCHAR2, p_seq INT)   

180.    RETURN VARCHAR2 IS  

181.    v_first INT;   

182.    v_last  INT;   

183.  BEGIN  

184. IF p_seq < 1 THEN

185.      RETURN NULL;   

186.    END IF;   

187.    IF p_seq = 1 THEN  

188.      IF instr(p_str, p_division, 1, p_seq) = 0 THEN  

1.        RETURN p_str;   

190.      ELSE  

191.        RETURN substr(p_str, 1, instr(p_str, p_division, 1) - 1);   

192.      END IF;   

193.    ELSE  

194.      v_first := instr(p_str, p_division, 1, p_seq - 1);   

195.      v_last  := instr(p_str, p_division, 1, p_seq);   

196.      IF (v_last = 0) THEN  

197. IF (v_first > 0) THEN

198.          RETURN substr(p_str, v_first + 1);   

199.        ELSE  

200.          RETURN NULL;   

201.        END IF;   

202.      ELSE  

203.        RETURN substr(p_str, v_first + 1, v_last - v_first - 1);   

204.      END IF;   

205.    END IF;   

206.  END f_split_str;   

207.  

208.  PROCEDURE p_rows_column(p_table      IN VARCHAR2,   

209.                          p_keep_cols  IN VARCHAR2,   

210.                          p_pivot_cols IN VARCHAR2,   

211.                          p_where      IN VARCHAR2 DEFAULT NULL,   

212.                          p_refc       IN OUT refc) IS  

213.    v_sql VARCHAR2(4000);   

214.    TYPE v_keep_ind_by IS TABLE OF VARCHAR2(4000) INDEX BY BINARY_INTEGER;   

215.    v_keep v_keep_ind_by;   

216.  

217.    TYPE v_pivot_ind_by IS TABLE OF VARCHAR2(4000) INDEX BY BINARY_INTEGER;   

218.    v_pivot v_pivot_ind_by;   

219.  

220.    v_keep_cnt   INT;   

221.    v_pivot_cnt  INT;   

222.    v_max_cols   INT;   

223.    v_partition  VARCHAR2(4000);   

224.    v_partition1 VARCHAR2(4000);   

225.    v_partition2 VARCHAR2(4000);   

226.  BEGIN  

227.    v_keep_cnt  := length(p_keep_cols) - length(REPLACE(p_keep_cols, ',')) + 1;   

228.    v_pivot_cnt := length(p_pivot_cols) -   

229.                   length(REPLACE(p_pivot_cols, ',')) + 1;   

230.    FOR i IN 1 .. v_keep_cnt LOOP   

231.      v_keep(i) := f_split_str(p_keep_cols, ',', i);   

232.    END LOOP;   

233.    FOR j IN 1 .. v_pivot_cnt LOOP   

234.      v_pivot(j) := f_split_str(p_pivot_cols, ',', j);   

235.    END LOOP;   

236.    v_sql := 'select max(count(*)) from ' || p_table || ' group by ';   

237.    FOR i IN 1 .. v_keep.LAST LOOP   

238.      v_sql := v_sql || v_keep(i) || ',';   

239.    END LOOP;   

240.    v_sql := rtrim(v_sql, ',');   

241.    EXECUTE IMMEDIATE v_sql   

242.      INTO v_max_cols;   

243.    v_partition := 'select ';   

244.    FOR x IN 1 .. v_keep.COUNT LOOP   

245.      v_partition1 := v_partition1 || v_keep(x) || ',';   

246.    END LOOP;   

247.    FOR y IN 1 .. v_pivot.COUNT LOOP   

248.      v_partition2 := v_partition2 || v_pivot(y) || ',';   

249.    END LOOP;   

250.    v_partition1 := rtrim(v_partition1, ',');   

251.    v_partition2 := rtrim(v_partition2, ',');   

252.    v_partition  := v_partition || v_partition1 || ',' || v_partition2 ||   

253.                    ', row_number() over (partition by ' || v_partition1 ||   

254.                    ' order by ' || v_partition2 || ') rn from ' || p_table;   

255.    v_partition  := rtrim(v_partition, ',');   

256.    v_sql        := 'select ';   

257.    FOR i IN 1 .. v_keep.COUNT LOOP   

258.      v_sql := v_sql || v_keep(i) || ',';   

259.    END LOOP;   

260.    FOR i IN 1 .. v_max_cols LOOP   

261.      FOR j IN 1 .. v_pivot.COUNT LOOP   

262.        v_sql := v_sql || ' max(decode(rn,' || i || ',' || v_pivot(j) ||   

263.                 ',null))' || v_pivot(j) || '_' || i || ',';   

2.      END LOOP;   

265.    END LOOP;   

266.    IF p_where IS NOT NULL THEN  

267.      v_sql := rtrim(v_sql, ',') || ' from (' || v_partition || ' ' ||   

268.               p_where || ') group by ';   

269.    ELSE  

270.      v_sql := rtrim(v_sql, ',') || ' from (' || v_partition ||   

271.               ') group by ';   

272.    END IF;   

273.    FOR i IN 1 .. v_keep.COUNT LOOP   

274.      v_sql := v_sql || v_keep(i) || ',';   

275.    END LOOP;   

276.    v_sql := rtrim(v_sql, ',');   

277.    p_print_sql(v_sql);   

278.    OPEN p_refc FOR v_sql;   

279.  EXCEPTION   

280.    WHEN OTHERS THEN  

281.      OPEN p_refc FOR  

282.        SELECT 'x' FROM dual WHERE 0 = 1;   

283.  END;   

284.  

285.  PROCEDURE p_rows_column_real(p_table     IN VARCHAR2,   

286.                               p_keep_cols IN VARCHAR2,   

287.                               p_pivot_col IN VARCHAR2,   

288.                               p_pivot_val IN VARCHAR2,   

2.                               p_where     IN VARCHAR2 DEFAULT NULL,   

290.                               p_refc      IN OUT refc) IS  

291.    v_sql VARCHAR2(4000);   

292.    TYPE v_keep_ind_by IS TABLE OF VARCHAR2(4000) INDEX BY BINARY_INTEGER;   

293.    v_keep v_keep_ind_by;   

294.    TYPE v_pivot_ind_by IS TABLE OF VARCHAR2(4000) INDEX BY BINARY_INTEGER;   

295.    v_pivot    v_pivot_ind_by;   

296.    v_keep_cnt INT;   

297.    v_group_by VARCHAR2(2000);   

298.  BEGIN  

299.    v_keep_cnt := length(p_keep_cols) - length(REPLACE(p_keep_cols, ',')) + 1;   

300.    FOR i IN 1 .. v_keep_cnt LOOP   

301.      v_keep(i) := f_split_str(p_keep_cols, ',', i);   

302.    END LOOP;   

303.    v_sql := 'select ' || 'cast(' || p_pivot_col ||   

304.             ' as varchar2(200)) as ' || p_pivot_col || ' from ' || p_table ||   

305.             ' group by ' || p_pivot_col;   

306.    EXECUTE IMMEDIATE v_sql BULK COLLECT   

307.      INTO v_pivot;   

308.    FOR i IN 1 .. v_keep.COUNT LOOP   

309.      v_group_by := v_group_by || v_keep(i) || ',';   

310.    END LOOP;   

311.    v_group_by := rtrim(v_group_by, ',');   

312.    v_sql      := 'select ' || v_group_by || ',';   

313.  

314.    FOR x IN 1 .. v_pivot.COUNT LOOP   

315.      v_sql := v_sql || ' max(decode(' || p_pivot_col || ',' || chr(39) ||   

316.               v_pivot(x) || chr(39) || ',' || p_pivot_val ||   

317.               ',null)) as "' || v_pivot(x) || '",';   

318.    END LOOP;   

319.    v_sql := rtrim(v_sql, ',');   

320.    IF p_where IS NOT NULL THEN  

321.      v_sql := v_sql || ' from ' || p_table || p_where || ' group by ' ||   

322.               v_group_by;   

323.    ELSE  

324.      v_sql := v_sql || ' from ' || p_table || ' group by ' || v_group_by;   

325.    END IF;   

326.    p_print_sql(v_sql);   

327.    OPEN p_refc FOR v_sql;   

328.  EXCEPTION   

329.    WHEN OTHERS THEN  

330.      OPEN p_refc FOR  

331.        SELECT 'x' FROM dual WHERE 0 = 1;   

332.  END;   

333.  

334.END;   

335./   

336.  

337.3.多列转换成字符串   

338.CREATE TABLE t_col_str AS  

339.SELECT * FROM t_col_row;   

340.  

341.这个比较简单，用||或concat函数可以实现：   

342.SELECT concat('a','b') FROM dual;   

343.  

344.1)|| OR concat   

345.适用范围：8i,9i,10g及以后版本   

346.SELECT * FROM t_col_str;   

347.  

348.SELECT ID,c1||','||c2||','||c3 AS c123   

349.FROM t_col_str;   

350.  

351.4.多行转换成字符串   

352.CREATE TABLE t_row_str(   

353.ID INT,   

354.col VARCHAR2(10));   

355.  

356.INSERT INTO t_row_str VALUES(1,'a');   

357.INSERT INTO t_row_str VALUES(1,'b');   

358.INSERT INTO t_row_str VALUES(1,'c');   

359.INSERT INTO t_row_str VALUES(2,'a');   

360.INSERT INTO t_row_str VALUES(2,'d');   

361.INSERT INTO t_row_str VALUES(2,'e');   

362.INSERT INTO t_row_str VALUES(3,'c');   

363.COMMIT;   

3.  

365.SELECT * FROM t_row_str;   

366.  

367.1)MAX + decode   

368.适用范围：8i,9i,10g及以后版本   

369.SELECT id,   

370.       MAX(decode(rn, 1, col, NULL)) ||   

371.       MAX(decode(rn, 2, ',' || col, NULL)) ||   

372.       MAX(decode(rn, 3, ',' || col, NULL)) str   

373.  FROM (SELECT id,   

374.               col,   

375.               row_number() over(PARTITION BY id ORDER BY col) AS rn   

376.          FROM t_row_str) t   

377.GROUP BY id   

378.ORDER BY 1;   

379.  

380.2)row_number + lead   

381.适用范围：8i,9i,10g及以后版本   

382.SELECT id, str   

383.  FROM (SELECT id,   

384.               row_number() over(PARTITION BY id ORDER BY col) AS rn,   

385.               col || lead(',' || col, 1) over(PARTITION BY id ORDER BY col) ||   

386.               lead(',' || col, 2) over(PARTITION BY id ORDER BY col) ||   

387.               lead(',' || col, 3) over(PARTITION BY id ORDER BY col) AS str   

388.          FROM t_row_str)   

3.WHERE rn = 1   

390.ORDER BY 1;   

391.  

392.3)MODEL   

393.适用范围：10g及以后版本   

394.SELECT id, substr(str, 2) str FROM t_row_str   

395.MODEL   

396.RETURN UPDATED ROWS  

397.PARTITION BY(ID)   

398.DIMENSION BY(row_number() over(PARTITION BY ID ORDER BY col) AS rn)   

399.MEASURES (CAST(col AS VARCHAR2(20)) AS str)   

400.RULES UPSERT   

401.ITERATE(3) UNTIL( presentv(str[iteration_number+2],1,0)=0)   

402.           (str[0] = str[0] || ',' || str[iteration_number+1])   

403.ORDER BY 1;              

404.  

405.4)sys_connect_by_path   

406.适用范围：8i,9i,10g及以后版本   

407.SELECT t.id id, MAX(substr(sys_connect_by_path(t.col, ','), 2)) str   

408.  FROM (SELECT id, col, row_number() over(PARTITION BY id ORDER BY col) rn   

409.          FROM t_row_str) t   

410.START WITH rn = 1   

411.CONNECT BY rn = PRIOR rn + 1   

412.       AND id = PRIOR id   

413.GROUP BY t.id;   

414.  

415.适用范围：10g及以后版本   

416.SELECT t.id id, substr(sys_connect_by_path(t.col, ','), 2) str   

417.  FROM (SELECT id, col, row_number() over(PARTITION BY id ORDER BY col) rn   

418.          FROM t_row_str) t   

419.WHERE connect_by_isleaf = 1   

420.START WITH rn = 1   

421.CONNECT BY rn = PRIOR rn + 1   

422.       AND id = PRIOR id;   

423.  

424.5)wmsys.wm_concat   

425.适用范围：10g及以后版本   

426.这个函数预定义按','分隔字符串，若要用其他符号分隔可以用，replace将','替换。   

427.  

428.SELECT id, REPLACE(wmsys.wm_concat(col), ',', '/')   

429.  FROM t_row_str   

430.GROUP BY id;   

431.  

432.5.字符串转换成多列   

433.其实际上就是一个字符串拆分的问题。   

434.  

435.CREATE TABLE t_str_col AS  

436.SELECT ID,c1||','||c2||','||c3 AS c123   

437.FROM t_col_str;   

438.  

439.SELECT * FROM t_str_col;   

440.  

441.1)substr + instr   

442.适用范围：8i,9i,10g及以后版本   

443.SELECT id,   

444.       c123,   

445.       substr(c123, 1, instr(c123 || ',', ',', 1, 1) - 1) c1,   

446.       substr(c123,   

447.              instr(c123 || ',', ',', 1, 1) + 1,   

448.              instr(c123 || ',', ',', 1, 2) - instr(c123 || ',', ',', 1, 1) - 1) c2,   

449.       substr(c123,   

450.              instr(c123 || ',', ',', 1, 2) + 1,   

451.              instr(c123 || ',', ',', 1, 3) - instr(c123 || ',', ',', 1, 2) - 1) c3   

452.  FROM t_str_col   

453.ORDER BY 1;   

454.  

455.2)regexp_substr   

456.适用范围：10g及以后版本   

457.SELECT id,   

458.       c123,   

459.       rtrim(regexp_substr(c123 || ',', '.*?' || ',', 1, 1), ',') AS c1,   

460.       rtrim(regexp_substr(c123 || ',', '.*?' || ',', 1, 2), ',') AS c2,   

461.       rtrim(regexp_substr(c123 || ',', '.*?' || ',', 1, 3), ',') AS c3   

462.  FROM t_str_col   

463.ORDER BY 1;   

4.  

465.6.字符串转换成多行   

466.CREATE TABLE t_str_row AS  

467.SELECT id,   

468.       MAX(decode(rn, 1, col, NULL)) ||   

469.       MAX(decode(rn, 2, ',' || col, NULL)) ||   

470.       MAX(decode(rn, 3, ',' || col, NULL)) str   

471.  FROM (SELECT id,   

472.               col,   

473.               row_number() over(PARTITION BY id ORDER BY col) AS rn   

474.          FROM t_row_str) t   

475.GROUP BY id   

476.ORDER BY 1;   

477.  

478.SELECT * FROM t_str_row;   

479.  

480.1)UNION ALL  

481.适用范围：8i,9i,10g及以后版本   

482.SELECT id, 1 AS p, substr(str, 1, instr(str || ',', ',', 1, 1) - 1) AS cv   

483.  FROM t_str_row   

484.UNION ALL  

485.SELECT id,   

486.       2 AS p,   

487.       substr(str,   

488.              instr(str || ',', ',', 1, 1) + 1,   

4.              instr(str || ',', ',', 1, 2) - instr(str || ',', ',', 1, 1) - 1) AS cv   

490.  FROM t_str_row   

491.UNION ALL  

492.SELECT id,   

493.       3 AS p,   

494.       substr(str,   

495.              instr(str || ',', ',', 1, 1) + 1,   

496.              instr(str || ',', ',', 1, 2) - instr(str || ',', ',', 1, 1) - 1) AS cv   

497.  FROM t_str_row   

498.ORDER BY 1, 2;   

499.  

500.适用范围：10g及以后版本   

501.SELECT id, 1 AS p, rtrim(regexp_substr(str||',', '.*?' || ',', 1, 1), ',') AS cv   

502.  FROM t_str_row   

503.UNION ALL  

504.SELECT id, 2 AS p, rtrim(regexp_substr(str||',', '.*?' || ',', 1, 2), ',') AS cv   

505.  FROM t_str_row   

506.UNION ALL  

507.SELECT id, 3 AS p, rtrim(regexp_substr(str||',', '.*?' || ',',1,3), ',') AS cv   

508.  FROM t_str_row   

509.ORDER BY 1, 2;   

510.  

511.2)VARRAY   

512.适用范围：8i,9i,10g及以后版本   

513.要创建一个可变数组：   

514.CREATE OR REPLACE TYPE ins_seq_type IS VARRAY(8) OF NUMBER;   

515.  

516.SELECT * FROM TABLE(ins_seq_type(1, 2, 3, 4, 5));   

517.  

518.SELECT t.id,   

519.       c.column_value AS p,   

520.       substr(t.ca,   

521.              instr(t.ca, ',', 1, c.column_value) + 1,   

522.              instr(t.ca, ',', 1, c.column_value + 1) -   

523.              (instr(t.ca, ',', 1, c.column_value) + 1)) AS cv   

524.  FROM (SELECT id,   

525.               ',' || str || ',' AS ca,   

526.               length(str || ',') - nvl(length(REPLACE(str, ',')), 0) AS cnt   

527.          FROM t_str_row) t   

528.INNER JOIN TABLE(ins_seq_type(1, 2, 3)) c ON c.column_value <=

529.                                                     t.cnt   

530.ORDER BY 1, 2;   

531.  

532.3)SEQUENCE series   

533.这类方法主要是要产生一个连续的整数列，产生连续整数列的方法有很多，主要有：   

534.CONNECT BY,ROWNUM+all_objects,CUBE等。   

535.适用范围：8i,9i,10g及以后版本   

536.SELECT t.id,   

537.       c.lv AS p,   

538.       substr(t.ca,   

539.              instr(t.ca, ',', 1, c.lv) + 1,   

540.              instr(t.ca, ',', 1, c.lv + 1) -   

541.              (instr(t.ca, ',', 1, c.lv) + 1)) AS cv   

542.  FROM (SELECT id,   

543.               ',' || str || ',' AS ca,   

544.               length(str || ',') - nvl(length(REPLACE(str, ',')), 0) AS cnt   

545.          FROM t_str_row) t,   

546. (SELECT LEVEL lv FROM dual CONNECT BY LEVEL <= 5) c

547.WHERE c.lv <= t.cnt

548.ORDER BY 1, 2;   

549.  

550.SELECT t.id,   

551.       c.rn AS p,   

552.       substr(t.ca,   

553.              instr(t.ca, ',', 1, c.rn) + 1,   

554.              instr(t.ca, ',', 1, c.rn + 1) -   

555.              (instr(t.ca, ',', 1, c.rn) + 1)) AS cv   

556.  FROM (SELECT id,   

557.               ',' || str || ',' AS ca,   

558.               length(str || ',') - nvl(length(REPLACE(str, ',')), 0) AS cnt   

559.          FROM t_str_row) t,   

560. (SELECT rownum rn FROM all_objects WHERE rownum <= 5) c

561.WHERE c.rn <= t.cnt

562.ORDER BY 1, 2;   

563.  

5.SELECT t.id,   

565.       c.cb AS p,   

566.       substr(t.ca,   

567.              instr(t.ca, ',', 1, c.cb) + 1,   

568.              instr(t.ca, ',', 1, c.cb + 1) -   

569.              (instr(t.ca, ',', 1, c.cb) + 1)) AS cv   

570.  FROM (SELECT id,   

571.               ',' || str || ',' AS ca,   

572.               length(str || ',') - nvl(length(REPLACE(str, ',')), 0) AS cnt   

573.          FROM t_str_row) t,   

574.       (SELECT rownum cb FROM (SELECT 1 FROM dual GROUP BY CUBE(1, 2))) c   

575.WHERE c.cb <= t.cnt

576.ORDER BY 1, 2;   

577.  

578.适用范围：10g及以后版本   

579.SELECT t.id,   

580.       c.lv AS p,   

581.       rtrim(regexp_substr(t.str || ',', '.*?' || ',', 1, c.lv), ',') AS cv   

582.  FROM (SELECT id,   

583.               str,   

584.               length(regexp_replace(str || ',', '[^' || ',' || ']', NULL)) AS cnt   

585.          FROM t_str_row) t   

586.INNER JOIN (SELECT LEVEL lv FROM dual CONNECT BY LEVEL <= 5) c ON c.lv <=

587.                                                                   t.cnt   

588.ORDER BY 1, 2;   

5.  

590.4)Hierarchical + DBMS_RANDOM   

591.适用范围：10g及以后版本   

592.SELECT id,   

593.       LEVEL AS p,   

594.       rtrim(regexp_substr(str || ',', '.*?' || ',', 1, LEVEL), ',') AS cv   

595.  FROM t_str_row   

596.CONNECT BY id = PRIOR id   

597.       AND PRIOR dbms_random.VALUE IS NOT NULL  

598. AND LEVEL <=

599.           length(regexp_replace(str || ',', '[^' || ',' || ']', NULL))   

600.ORDER BY 1, 2;   

601.  

602.5)Hierarchical + CONNECT_BY_ROOT   

603.适用范围：10g及以后版本   

604.SELECT id,   

605.       LEVEL AS p,   

606.       rtrim(regexp_substr(str || ',', '.*?' || ',', 1, LEVEL), ',') AS cv   

607.  FROM t_str_row   

608.CONNECT BY id = connect_by_root id   

609. AND LEVEL <=

610.           length(regexp_replace(str || ',', '[^' || ',' || ']', NULL))   

611.ORDER BY 1, 2;   

612.  

613.6)MODEL   

614.适用范围：10g及以后版本   

615.SELECT id, p, cv FROM t_str_row   

616.MODEL   

617.RETURN UPDATED ROWS  

618.PARTITION BY(ID)   

619.DIMENSION BY( 0 AS p)   

620.MEASURES( str||',' AS cv)   

621.RULES UPSERT   

622.  (cv   

623.   [ FOR p   

624.        FROM 1 TO length(regexp_replace(cv[0],'[^'||','||']',null))   

625.        INCREMENT 1   

626.   ] = rtrim(regexp_substr( cv[0],'.*?'||',',1,cv(p)),','))   

627.ORDER BY 1,2;   

628.  

629.end.  

本文来自鸿雁博客，转载请标明出处：http://www.cnocp.com/archives/34.html