oracle行列转换-多行转换成字符串

建表语句及初始化数据脚本如下：

点击(此处)折叠或打开

1. SQL> create table t_row_str(
   
2.   2 id int,
   
3.   3 col varchar2(10));
   
4. 
   
5. 表已创建。
   
6. 
   
7. SQL> insert into t_row_str values(1,'a');
   
8. 
   
9. 已创建 1 行。
   
10. 
    
11. SQL> insert into t_row_str values(1,'b');
    
12. 
    
13. 已创建 1 行。
    
14. 
    
15. SQL> insert into t_row_str values(1,'c');
    
16. 
    
17. 已创建 1 行。
    
18. 
    
19. SQL> insert into t_row_str values(2,'a');
    
20. 
    
21. 已创建 1 行。
    
22. 
    
23. SQL> insert into t_row_str values(2,'d');
    
24. 
    
25. 已创建 1 行。
    
26. 
    
27. SQL> insert into t_row_str values(2,'e');
    
28. 
    
29. 已创建 1 行。
    
30. 
    
31. SQL> insert into t_row_str values(3,'c');
    
32. 
    
33. 已创建 1 行。
    
34. 
    
35. SQL> commit;
    
36. 
    
37. 提交完成。
    
38. 
    
39. SQL> select * from t_row_str;
    
40. 
    
41.         ID COL
    
42. ---------- ----------
    
43. 
    
44.          1 a
    
45.          1 b
    
46.          1 c
    
47.          2 a
    
48.          2 d
    
49.          2 e
    
50.          3 c
    
51. 
    
52. 已选择7行。

多行转换成字符串有以下多种方式
1)MAX + decode
适用范围：8i,9i,10g及以后版本

点击(此处)折叠或打开

1. SQL> select id,max(decode(rn,1,col,null))||
   
2.   2 max(decode(rn,2,','||col,null))||
   
3.   3 max(decode(rn,3,','||col,null)) str
   
4.   4 from(select id,col,row_number() over(partition by id order by col) as rn
   
5.   5 from t_row_str) t
   
6.   6 group by id
   
7.   7 order by 1;
   
8. 
   
9.         ID STR
   
10. ---------- --------------------------------
    
11. 
    
12.          1 a,b,c
    
13.          2 a,d,e
    
14.          3 c

2) row_number + lead(适用范围：8i,9i,10g及以后版本)

点击(此处)折叠或打开

1. SQL> select id,str from (select id,row_number() over(partition by id order by col) as
   
2.   2 rn,
   
3.   3 col||lead(','||col,1) over(partition by id order by col)||lead(','||col,2) over(partition by id
   
4.  order by col) as str
   
5.   4 from t_row_str)
   
6.   5 where rn=1
   
7.   6 order by 1
   
8.   7 /
   
9. 
   
10.         ID STR
    
11. ---------- --------------------------------
    
12. 
    
13.          1 a,b,c
    
14.          2 a,d,e
    
15.          3 c

注：oracle的lead函数是偏移量函数，其用途是访问某一字段的下一个值（lag函数用法一样，只是访问上一个值）。如下代码所示：

点击(此处)折叠或打开

1. SQL> select id,col,lead(col,1) over(partition by id order by col) lead_1,
   
2.   2 lead(col,2) over(partition by id order by col) lead_2,
   
3.   3 lead(col,3) over(partition by id order by col) lead_3
   
4.   4 from t_row_str;
   
5. 
   
6.         ID COL LEAD_1 LEAD_2 LEAD_3
   
7. ---------- ---------- ---------- ---------- ----------
   
8. 
   
9.          1 a b c
   
10.          1 b c
    
11.          1 c
    
12.          2 a d e
    
13.          2 d e
    
14.          2 e
    
15.          3 c
    
16. 
    
17. 已选择7行。

3)MODEL
适用范围：10g及以后版本

点击(此处)折叠或打开

1. SQL> SELECT id, substr(str, 2) str FROM t_row_str
   
2.   2 MODEL
   
3.   3 RETURN UPDATED ROWS
   
4.   4 PARTITION BY(ID)
   
5.   5 DIMENSION BY(row_number() over(PARTITION BY ID ORDER BY col) AS rn)
   
6.   6 MEASURES (CAST(col AS VARCHAR2(20)) AS str)
   
7.   7 RULES UPSERT
   
8.   8 ITERATE(3) UNTIL( presentv(str[iteration_number+2],1,0)=0)
   
9.   9 (str[0] = str[0] || ',' || str[iteration_number+1])
   
10.  10 ORDER BY 1;
    
11. 
    
12.         ID STR
    
13. ---------- --------------------------------------
    
14. 
    
15.          1 a,b,c
    
16.          2 a,d,e
    
17.          3 c

4)sys_connect_by_path
适用范围：8i,9i,10g及以后版本

点击(此处)折叠或打开

1. SQL> edit;
   
2. 已写入 file afiedt.buf
   
3. 
   
4.   1 SELECT t.id id, MAX(substr(sys_connect_by_path(t.col, ','), 2)) str
   
5.   2 FROM (SELECT id, col, row_number() over(PARTITION BY id ORDER BY col) rn
   
6.   3 FROM t_row_str) t
   
7.   4 START WITH rn = 1
   
8.   5 CONNECT BY rn = PRIOR rn + 1
   
9.   6 AND id = PRIOR id
   
10.   7* GROUP BY t.id
    
11. SQL> /
    
12. 
    
13.        ID STR
    
14. ---------- --------------------------------------
    
15. 
    
16.          1 a,b,c
    
17.          2 a,d,e
    
18.          3 c
20. 以下代码适用于10G及之后的版本
    
21. SQL> edit
    
22. 已写入 file afiedt.buf
    
23. 
    
24.   1 SELECT t.id id, substr(sys_connect_by_path(t.col, ','), 2) str
    
25.   2 FROM (SELECT id, col, row_number() over(PARTITION BY id ORDER BY col) rn
    
26.   3 FROM t_row_str) t
    
27.   4 where connect_by_isleaf=1
    
28.   5 START WITH rn = 1
    
29.   6 CONNECT BY rn = PRIOR rn + 1
    
30.   7* AND id = PRIOR id
    
31. SQL> /
    
32. 
    
33.        ID STR
    
34. ---------- --------------------------------------
    
35. 
    
36.          1 a,b,c
    
37.          2 a,d,e
    
38.          3 c

注：sys_connect_by_path函数使用之前，必须先构造出一个树，否则无法使用，上例用row_number函数构造树型结构。
5)wmsys.wm_concat
适用范围：10g及以后版本
这个函数预定义按','分隔字符串，若要用其他符号分隔可以用，replace将','替换。

点击(此处)折叠或打开

1. SQL> select id,wmsys.wm_concat(col) from t_row_str
   
2.   2 group by id;
   
3. 
   
4.         ID
   
5. ----------
   
6. 
   
7. WMSYS.WM_CONCAT(COL)
   
8. -------------------------------------------------------
   
9. 
   
10.          1
    
11. a,b,c
    
12. 
    
13.          2
    
14. a,d,e
    
15. 
    
16.          3
    
17. c

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/21251711/viewspace-1066878/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/21251711/viewspace-1066878/