请问如下转换如何只用查询就可以实现?
COL1 COL2 COL3
A 1 who
A 2 are
A 3 you
B 1
B 2 thank
B 3 you转化后:
COLA COLB
A who, are, you
B thank, you请注意COL3可能存在空值情况。
感谢!
COL1 COL2 COL3
A 1 who
A 2 are
A 3 you
B 1
B 2 thank
B 3 you转化后:
COLA COLB
A who, are, you
B thank, you请注意COL3可能存在空值情况。
感谢!
解决方案 »
- oracle安装问题,64位的系统
- 当oracle数据改变时,如何获取改变的内容
- 大侠们帮我优化一下SQL ORACLE 速度太慢 要40多秒
- 数据查询的问题
- select * from table where number in ()语句的特殊用法问题。
- 新手安装oracle9i后用户名密码无法登录!
- 关于long raw数据的一个问题
- select d_count := count(*) from XCARLOCK; 语法那里错了? 谢谢
- 如何编写该sql语句?? 谢谢
- 高分,请问XP上能安装orcale9i的什么版本?个人,标准??要服务(server)
- emca -config dbcontrol db -repos create后OEM提示:无法连接到数据库实例。在线等。
- 关于监听程序的问题
from
(select COL1,COL3 from tb where COL2=1) a1,
(select COL1,COL3 from tb where COL2=2) a2,
(select COL1,COL3 from tb where COL2=3) a3
where a1.COL1=a2.COL1 and a1.COL1=a3.COL1
空值用is not null过滤记录就可以了
9I用SYS_CONNECT_BY_PATH
看此贴
http://topic.csdn.net/u/20080505/11/a0958b42-d938-465f-972a-0f61a2969c97.html?seed=491226048
from tb
group by col1
from tb
where col3 is not null
group by col1
/*
标题:合并相同列的数据
作者:爱新觉罗.毓华
时间:2008-05-05
地点:广东深圳
*//*情况说明
NAME USERID
张三 KB001
张三 KB003
李四 KB001
李四 KB002
李四 KB003用SQL实现表示如下:
NAME USERID
张三 KB001,KB003
李四 KB001,KB002,KB003
*/create table tb(NAME varchar2(10) , USERID varchar2(10))
insert into tb values('张三' , 'KB001');
insert into tb values('张三' , 'KB003');
insert into tb values('李四' , 'KB001');
insert into tb values('李四' , 'KB002');
insert into tb values('李四' , 'KB003');--1
select name,rtrim(
max(decode(USERID , 'KB001' , USERID || ',' , '')) ||
max(decode(USERID , 'KB002' , USERID || ',' , '')) ||
max(decode(USERID , 'KB003' , USERID || ',' , '')),',') userid
from tb
group by name--2
SELECT NAME, ltrim(MAX(sys_connect_by_path(userid , ',')) , ',') userid
FROM (SELECT NAME , userid , row_number() over(PARTITION BY NAME ORDER BY userid) rn , rownum prn FROM tb)
START WITH rn = 1
CONNECT BY prn - 1 = PRIOR prn AND NAME = PRIOR NAME
GROUP BY NAME
ORDER BY NAME;drop table tb/*
NAME USERID
---------- ---------------------------------
李四 KB001,KB002,KB003
张三 KB001,KB003
2 rows selected
*/
--------------------------------
create table t2(col1 varchar2(10),col2 varchar2(10));
insert into t2 values('001','vl1');
insert into t2 values('001','vl2');
insert into t2 values('001','vl3');
insert into t2 values('002','vl1');
insert into t2 values('002','vl2'); SELECT COL1, LTRIM(MAX(SYS_CONNECT_BY_PATH(COL2, ',')), ',') COL2
FROM (SELECT COL1,
COL2,
MIN(COL2) OVER(PARTITION BY COL1) COL2_MIN,
(ROW_NUMBER() OVER(ORDER BY COL1, COL2)) +
(DENSE_RANK() OVER(ORDER BY COL1)) NUMID
FROM T2)
START WITH COL2 = COL2_MIN
CONNECT BY NUMID - 1 = PRIOR NUMID
GROUP BY COL1 /*
COL1 COL2
---------- ----------------------------------------
001 vl1,vl2,vl3
002 vl1,vl2
2 rows selected
*/