举例,现有一个5行2列的表:
1 a
1 b
1 c
2 d
2 e
能不能用一条select语句查出这样的结果:
1 abc
2 de盼高手答复,困扰好久了...
1 a
1 b
1 c
2 d
2 e
能不能用一条select语句查出这样的结果:
1 abc
2 de盼高手答复,困扰好久了...
解决方案 »
- 如何实现计费管理系统的整合,急求!
- 设置db_cache_size的问题
- oracle 行转列
- plsql查看字段报错 Access violation at address 0042
- Oracle11g 手工建库时报错!求帮忙!很急!
- Oracle中如何实现自动编号功能
- oracle的存储过程中关与锁技术的运用求助。
- 在线等(ORACLE安装)
- 当ORACLE8.1.7中的某一表的数据有改动时,怎样将这个表的数据时时反映到SQL SERVER 2000的某一表中?
- 帮助,什么意思??
- 急!三天了!oracle10g服务能正常启动,可是SQLPLUS连接不了,“TNS:监听进程不能解析在连接描述符中给出的SERVICE_NAME”
- Oracle Olap api的开发问题(急)
如
create table tb
(
id number,
name varchar2(100)
);
insert into tb values(1,'aa');
insert into tb values(2,'bb');
insert into tb values(3,'cc');
insert into tb values(1,'dd');
insert into tb values(1,'ee'); 实现
1 aa,dd,ee
2 bb
3 cc
--------------------------create or replace function t_f
(
v_id number
)
return varchar2
is
result varchar2(5000);
begin
result := '';
for x in (select name from tb where id = v_id)
loop
result := result || x.name || ',';
end loop; result := substr(result, 1, length(result) - 1);
return result;
end t_f;
select id, t_f(id) as name from (select distinct id from tb) x
----------------下面是例子
Oracle9i中使用SYS_CONNECT_BY_PATH进行行列转换- -
有表:
SQL> SELECT deptno, ename FROM emp ORDER BY deptno, ename; DEPTNO ENAME
------ ----------
10 CLARK
10 KING
10 MILLER
20 ADAMS
20 FORD
20 JONES
20 SCOTT
20 SMITH
30 ALLEN
30 BLAKE
30 JAMES
30 MARTIN
30 TURNER
30 WARD 14 rows selected.
想输出为:
DEPTNO ENAME
------ ----------
10 CLARK, KING, MILLER
20 ADAMS, FORD, JONES, SCOTT, SMITH
30 ALLEN, BLAKE, JAMES, MARTIN, TURNER, WARD 除了使用聚集函数或者存储过程之外,9i中可以:
SQL> SELECT deptno
2 , LTRIM(MAX(SYS_CONNECT_BY_PATH(ename,','))
3 KEEP (DENSE_RANK LAST ORDER BY curr),',') AS concatenated
4 FROM ( SELECT deptno
5 , ename
6 , ROW_NUMBER() OVER (PARTITION BY deptno ORDER BY ename) AS curr
7 , ROW_NUMBER() OVER (PARTITION BY deptno ORDER BY ename) -1 AS prev
8 FROM emp )
9 GROUP BY deptno
10 CONNECT BY prev = PRIOR curr AND deptno = PRIOR deptno
11 START WITH curr = 1;
DEPTNO----------CONCATENATED----------------------------------------------------------------------------------------------------
10CLARK,KING,MILLER
20ADAMS,FORD,JONES,SCOTT,SMITH
30ALLEN,BLAKE,JAMES,MARTIN,TURNER,WARD10CLARK,KING,MILLER 20ADAMS,FORD,JONES,SCOTT,SMITH 30ALLEN,BLAKE,JAMES,MARTIN,TURNER,WARD
肯定收集了好多帖子收藏的和我收藏的是一样的
select id, ltrim(max(sys_connect_by_path(name,',')) keep(dense_rank last or
der by curr),',') as concatenated from (select id, name, row_number() over (part
ition by id order by name) as curr, row_number() over (partition by id order by
name) -1 as prev from t1)
group by id
connect by prev=prior curr and id = prior id
start with curr=1;
2 UNION
3 SELECT 1 ID,'B' NAME FROM DUAL
4 UNION
5 SELECT 1 ID,'C' NAME FROM DUAL
6 UNION
7 SELECT 2 ID,'D' NAME FROM DUAL
8 UNION
9 SELECT 2 ID,'E' NAME FROM DUAL
10 )
11 select ID,WMSYS.WM_CONCAT(NAME) NAME
12 FROM A
13 GROUP BY ID
SQL> /
ID NAME
---------- --------------------------------------------------------------------------------
1 A,B,C
2 D,E
(select 1 col1,'a' col2 from dual
union all
select 1,'b' from dual
union all
select 1,'c' from dual
union all
select 2,'d' from dual
union all
select 2,'e' from dual
)
select col1,replace(wm_concat(col2),',','') from tb
group by col1