表中的数据是Name Course
zhou chinese
zhou maths
zhou english
liu georaphy
liu english
怎么写sql语句,可以select出下面的结果
Name Courses
zhou chinese; maths; english
liu georaphy; english注意,当中是用“一个分号 + 一个空格”来分割的,而不是用逗号
zhou chinese
zhou maths
zhou english
liu georaphy
liu english
怎么写sql语句,可以select出下面的结果
Name Courses
zhou chinese; maths; english
liu georaphy; english注意,当中是用“一个分号 + 一个空格”来分割的,而不是用逗号
解决方案 »
- 帮忙做个很简单的实验吧。谢谢了
- 触发器不能对同一张表操作吗
- Oracle如何重建表空间
- oracle WEB 开发. 后台提交FORM -- 急 .... 在线等待
- 求sql
- 为何我控制台内--进放cd disk1 # ./runInstaller & 提示没有这个文件夹呢?大家帮我看看吧,谢谢了,高分相送!
- 请问如何登陆oracle10g?给50分
- 应用程序双击启动一会后就消失,无法打开.log_file ,trc_file,log_status文件显示如下错误:
- ORACLE数据库语句是否有长度限制?
- 请各位指点迷津,谢谢!
- 请大家介绍一本关于 oracle 和 SQL 的学习书籍吧 谢谢~
- oracle10g 怎么用创建表时候的语句添加默认值?
from table_name
group by name
create table t1 (
name varchar2(10),
course varchar2(15)
)insert into t1 values('zhou','chinese')
insert into t1 values('zhou','maths')
insert into t1 values('zhou','english')
insert into t1 values('liu','georaphy')
insert into t1 values('liu','english')
---------sql statment---------
select name,
decode(substr(courses, -1),
';',
substr(courses, 1, length(courses) - 1),
courses)
from (select name,
max(decode(rn, 1, course, null)) || ';' ||
max(decode(rn, 2, course, null)) || ';' ||
max(decode(rn, 3, course, null)) courses
from (select name,
course,
row_number() over(partition by name order by course) rn
from t1)
group by name order by name desc);
---------------------result -------------------
1 zhou chinese;english;maths
2 liu english;georaphy
name varchar2(10),
course varchar2(15)
)insert into t1 values('zhou','chinese')
insert into t1 values('zhou','maths')
insert into t1 values('zhou','english')
insert into t1 values('liu','georaphy')
insert into t1 values('liu','english')select * from t1select name,
decode(substr(courses, -1),
';',
substr(courses, 1, length(courses)-1),
courses) courses
from (select name,
max(decode(rn, 1, course, null)) || ';' ||
max(decode(rn, 2, course, null)) || ';' ||
max(decode(rn, 3, course, null)) courses
from (select name,
course,
row_number() over(partition by name order by course) rn
from t1)
group by name order by name desc);
select name,replace(wmsys.wm_concat(course),',','; ') courses
from table_name
group by name--oracle 9i及9i以下
select name,
max(replace(substr(sys_connect_by_path(course, '*'), 2),'*','; ') )
from(
select name,course,
dense_rank()over(order by name) + row_number()over(order by name) rid,
row_number() over (partition by name order by name) nid
from table_name)
start with nid = 1
connect by prior rid = rid - 1
group by name;