oracle行列转换总结.doc

最近不少人提的问题都与行列转换有关系，所以我对行列转换的相关知识做了一个总结，希望对大家有所帮助，同时有何错疏，恳请大家指出，我也是在写作过程中学习，算是一起和大家学习吧。在写作的过程中也参考了一些资料和网友的文章，在此表示感谢。行列转换包括以下六种情况：*列转行*行转列*多列转换成字符串*多行转换成字符串*字符串转换成多列*字符串转换成多行下面分别进行举例介绍。首先声明一点，有些例子需要如下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. cn1 = c1, 44. cn2 = c2, 45. cn3 = c3, 46. cv1 = c10, 47. cv2 = c20, 48. cv3 = c30 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, 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 64.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. 89.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, 164. 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 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 | ,; 264. 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, 289. 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; 364. 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) 389.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(striteration_number+2,1,0)=0) 402. (str0 = str0 | , | striteration_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; 464. 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, , |