建表语句及初始化数据脚本如下：
多行转换成字符串有以下多种方式
1)MAX + decode
适用范围：8i,9i,10g及以后版本
2)row_number + lead(适用范围：8i,9i,10g及以后版本)
点击(此处)折叠或打开
-
SQL> create table t_row_str(
-
2 id int,
-
3 col varchar2(10));
-
-
表已创建。
-
-
SQL> insert into t_row_str values(1,'a');
-
-
已创建 1 行。
-
-
SQL> insert into t_row_str values(1,'b');
-
-
已创建 1 行。
-
-
SQL> insert into t_row_str values(1,'c');
-
-
已创建 1 行。
-
-
SQL> insert into t_row_str values(2,'a');
-
-
已创建 1 行。
-
-
SQL> insert into t_row_str values(2,'d');
-
-
已创建 1 行。
-
-
SQL> insert into t_row_str values(2,'e');
-
-
已创建 1 行。
-
-
SQL> insert into t_row_str values(3,'c');
-
-
已创建 1 行。
-
-
SQL> commit;
-
-
提交完成。
-
-
SQL> select * from t_row_str;
-
-
ID COL
-
---------- ----------
-
-
1 a
-
1 b
-
1 c
-
2 a
-
2 d
-
2 e
-
3 c
-
- 已选择7行。
1)MAX + decode
适用范围：8i,9i,10g及以后版本
点击(此处)折叠或打开
-
SQL> select id,max(decode(rn,1,col,null))||
-
2 max(decode(rn,2,','||col,null))||
-
3 max(decode(rn,3,','||col,null)) str
-
4 from(select id,col,row_number() over(partition by id order by col) as rn
-
5 from t_row_str) t
-
6 group by id
-
7 order by 1;
-
-
ID STR
-
---------- --------------------------------
-
-
1 a,b,c
-
2 a,d,e
- 3 c
点击(此处)折叠或打开
-
SQL> select id,str from (select id,row_number() over(partition by id order by col) as
-
2 rn,
-
3 col||lead(','||col,1) over(partition by id order by col)||lead(','||col,2) over(partition by id
-
order by col) as str
-
4 from t_row_str)
-
5 where rn=1
-
6 order by 1
-
7 /
-
-
ID STR
-
---------- --------------------------------
-
-
1 a,b,c
-
2 a,d,e
- 3 c
注：oracle的lead函数是偏移量函数，其用途是访问某一字段的下一个值（lag函数用法一样，只是访问上一个值）。如下代码所示：
3)MODEL
适用范围：10g及以后版本
4)sys_connect_by_path
适用范围：8i,9i,10g及以后版本
注：sys_connect_by_path函数使用之前，必须先构造出一个树，否则无法使用，上例用row_number函数构造树型结构。
5)wmsys.wm_concat
适用范围：10g及以后版本
这个函数预定义按','分隔字符串，若要用其他符号分隔可以用，replace将','替换。
点击(此处)折叠或打开
-
SQL> select id,col,lead(col,1) over(partition by id order by col) lead_1,
-
2 lead(col,2) over(partition by id order by col) lead_2,
-
3 lead(col,3) over(partition by id order by col) lead_3
-
4 from t_row_str;
-
-
ID COL LEAD_1 LEAD_2 LEAD_3
-
---------- ---------- ---------- ---------- ----------
-
-
1 a b c
-
1 b c
-
1 c
-
2 a d e
-
2 d e
-
2 e
-
3 c
-
- 已选择7行。
3)MODEL
适用范围：10g及以后版本
点击(此处)折叠或打开
-
SQL> SELECT id, substr(str, 2) str FROM t_row_str
-
2 MODEL
-
3 RETURN UPDATED ROWS
-
4 PARTITION BY(ID)
-
5 DIMENSION BY(row_number() over(PARTITION BY ID ORDER BY col) AS rn)
-
6 MEASURES (CAST(col AS VARCHAR2(20)) AS str)
-
7 RULES UPSERT
-
8 ITERATE(3) UNTIL( presentv(str[iteration_number+2],1,0)=0)
-
9 (str[0] = str[0] || ',' || str[iteration_number+1])
-
10 ORDER BY 1;
-
-
ID STR
-
---------- --------------------------------------
-
-
1 a,b,c
-
2 a,d,e
- 3 c
4)sys_connect_by_path
适用范围：8i,9i,10g及以后版本
点击(此处)折叠或打开
-
SQL> edit;
-
已写入 file afiedt.buf
-
-
1 SELECT t.id id, MAX(substr(sys_connect_by_path(t.col, ','), 2)) str
-
2 FROM (SELECT id, col, row_number() over(PARTITION BY id ORDER BY col) rn
-
3 FROM t_row_str) t
-
4 START WITH rn = 1
-
5 CONNECT BY rn = PRIOR rn + 1
-
6 AND id = PRIOR id
-
7* GROUP BY t.id
-
SQL> /
-
-
ID STR
-
---------- --------------------------------------
-
-
1 a,b,c
-
2 a,d,e
- 3 c
-
以下代码适用于10G及之后的版本
-
SQL> edit
-
已写入 file afiedt.buf
-
-
1 SELECT t.id id, substr(sys_connect_by_path(t.col, ','), 2) str
-
2 FROM (SELECT id, col, row_number() over(PARTITION BY id ORDER BY col) rn
-
3 FROM t_row_str) t
-
4 where connect_by_isleaf=1
-
5 START WITH rn = 1
-
6 CONNECT BY rn = PRIOR rn + 1
-
7* AND id = PRIOR id
-
SQL> /
-
-
ID STR
-
---------- --------------------------------------
-
-
1 a,b,c
-
2 a,d,e
- 3 c
5)wmsys.wm_concat
适用范围：10g及以后版本
这个函数预定义按','分隔字符串，若要用其他符号分隔可以用，replace将','替换。
点击(此处)折叠或打开
-
SQL> select id,wmsys.wm_concat(col) from t_row_str
-
2 group by id;
-
-
ID
-
----------
-
-
WMSYS.WM_CONCAT(COL)
-
-------------------------------------------------------
-
-
1
-
a,b,c
-
-
2
-
a,d,e
-
-
3
- c
来自 “ ITPUB博客 ” ，链接：https://blog.itpub.net/21251711/viewspace-1066878/ ，如需转载，请注明出处，否则将追究法律责任。
上一篇：
oracle行列转换-多列转换成字符串
下一篇：
oracle行列转换-字符串转换成多列
请登录后发表评论
登录
全部评论
