表grade | name
----------------
A XX
A XY
A YY
B aa
B bb
如何得出下表
grade | name
----------------
A XX;XY;YY
B aa;bb求sql
----------------
A XX
A XY
A YY
B aa
B bb
如何得出下表
grade | name
----------------
A XX;XY;YY
B aa;bb求sql
解决方案 »
- ORA-08002:序列SEQ_GID.CURRVAL尚未在此进程中定义
- 求救!!!数据恢复问题,怎么在一台机器上恢复别一台机器上的ORL文件夹下的数据库文件???
- oracle11g rac内存使用率不断上升,从开始的50%现在已超过90%,可能是什么原因,如何降低使用率,谢谢!
- oracle怎么那么难用?!!
- 几个基础的问题!
- Oracle中可不可以用E/R图显示各表间关系(就像ERWin或Rose建模显示的那样)
- 数据库设计的一个问题
- 紧急求助! 如何定义二维数组
- 奇怪的问题
- 感谢以下朋友:bzszp(SongZip), blue__star(蓝色沸点) ,mimimi(开心果) , jlandzpa(ORA-00600),barrytyh(海阔天空)
- 请教一个sql语句
- oracle 用户角色问题
(SELECT 'A' id,'XX' name FROM dual
UNION ALL
SELECT 'A' id,'XY' name from dual
UNION ALL
SELECT 'A' id,'YY' name from dual
UNION ALL
SELECT 'B' id,'aa' name from dual
UNION ALL
SELECT 'B' id,'bb' name from dual)
SELECT id,REPLACE(wm_concat(name),',',';') FROM temp
GROUP BY id;
SQL> with t as(
2 select 'A' grade,'XX'name from dual union all
3 select 'A','XY' from dual union all
4 select 'A','YY' from dual union all
5 select 'B','aa' from dual union all
6 select 'B','bb' from dual
7 )
8 select grade,wmsys.wm_concat(name) name
9 from t
10 group by grade;GRADE NAME
----- --------------------------------------------------------------------------------
A XX,XY,YY
B aa,bb
--考虑分号之间连接的name如果有顺序,这样:
--例按name升序组合:select grade,max(replace(name,',',';')) name from (
select grade,wm_concat(name)over(partition by grade order by name) name
from tab
)
group by grade
with t as(
select 'A' grade,'XX'name from dual union all
select 'A','XY' from dual union all
select 'A','YY' from dual union all
select 'B','aa' from dual union all
select 'B','bb' from dual
)
select grade,replace(wm_concat(name),',',';') name
from t
group by grade;
GRADE NAME
----- --------------------------------------------------------------------------------
A XX;XY;YY
B aa;bb
SQL> with tb as(
2 select 'A' grade,'XX'name from dual union all
3 select 'A','XY' from dual union all
4 select 'A','YY' from dual union all
5 select 'B','aa' from dual union all
6 select 'B','bb' from dual
7 )
8 select grade,substr(max(sys_connect_by_path(name,';')),2) name
9 from (select grade,name,row_number() over(partition by grade order by name) rn from tb)
10 start with rn=1
11 connect by rn= prior rn+1 and connect_by_root(grade)=grade
12 group by grade
13 /
GRADE NAME
----- --------------------------------------------------------------------------------
A XX;XY;YY
B aa;bb
SQL> ed
SQL>
SQL> with tb as(
2 select 'A' grade,'XX'name from dual union all
3 select 'A','XY' from dual union all
4 select 'A','YY' from dual union all
5 select 'B','aa' from dual union all
6 select 'B','bb' from dual
7 )
8 select grade,wm_concat(name) from tb group by grade
9 /
GRADE WM_CONCAT(NAME)
----- --------------------------------------------------------------------------------
A XX,XY,YY
B aa,bb