select 别名 from(
select row_number()over(order by lv desc) id,name from
(
select level lv,replace(sys_connect_by_path(字段,','),',','') 别名from
(
select name,row_number()over(order by 字段) id from 查询结果
)
connect by prior id = id-1
)
)where id=1;
*************
测试log:
*************
[TEST@ORA1] SQL>select * from test2;
NAM ID
--- ----------
aaa 234
bbb 786
ccc 879
[TEST@ORA1] SQL>select name from(
2 select row_number()over(order by lv desc) id,name from
3 (
4 select level lv,replace(sys_connect_by_path(name,','),',','') name from
5 (
6 select name,row_number()over(order by name) id from test2
7 )
8 connect by prior id = id-1
9 )
10 )where id=1;
NAME
----------------------------------
aaabbbccc
*************************
10g以上(含10g):
select replace(wm_concat(字段),',','') 别名 from 查询结果;
测试log:
[TEST@ORA1] SQL>select * from test2;
NAM ID
--- ----------
aaa 234
bbb 786
ccc 879
[TEST@ORA1] SQL>select replace(wm_concat(name),',','') name from test2;
NAME
-----------------------------------------
aaabbbccc[@more@]
select row_number()over(order by lv desc) id,name from
(
select level lv,replace(sys_connect_by_path(字段,','),',','') 别名from
(
select name,row_number()over(order by 字段) id from 查询结果
)
connect by prior id = id-1
)
)where id=1;
*************
测试log:
*************
[TEST@ORA1] SQL>select * from test2;
NAM ID
--- ----------
aaa 234
bbb 786
ccc 879
[TEST@ORA1] SQL>select name from(
2 select row_number()over(order by lv desc) id,name from
3 (
4 select level lv,replace(sys_connect_by_path(name,','),',','') name from
5 (
6 select name,row_number()over(order by name) id from test2
7 )
8 connect by prior id = id-1
9 )
10 )where id=1;
NAME
----------------------------------
aaabbbccc
*************************
10g以上(含10g):
select replace(wm_concat(字段),',','') 别名 from 查询结果;
测试log:
[TEST@ORA1] SQL>select * from test2;
NAM ID
--- ----------
aaa 234
bbb 786
ccc 879
[TEST@ORA1] SQL>select replace(wm_concat(name),',','') name from test2;
NAME
-----------------------------------------
aaabbbccc[@more@]
来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/106285/viewspace-1027020/,如需转载,请注明出处,否则将追究法律责任。
转载于:http://blog.itpub.net/106285/viewspace-1027020/