現查詢幾張表得出兩行結果(有可能三行四行或更多)如下:
A090612001 A=B 110*345*470
A090612001 A=C 110*345*270
A090612001 A=C 110*345*470
可否合並寫成結果為:A090612001 A=B||A=C 110*345*470||110*345*270
該如何寫SQL,謝謝各位大俠提示!
A090612001 A=B 110*345*470
A090612001 A=C 110*345*270
A090612001 A=C 110*345*470
可否合並寫成結果為:A090612001 A=B||A=C 110*345*470||110*345*270
該如何寫SQL,謝謝各位大俠提示!
解决方案 »
- winform 连不上 服务器的oracle数据库,提示“Settlement.DalSettlement.OracleHelper”的类型初始值设定项引发异常
- 存储过程优化
- 关于Oracle的行列转换
- 执行Oracle存储过程显示结果问题
- iSQL*Plus URL 为:http://xxx:5560/isqlplusiSQL*Plus
- 造成ORACLE-29260错误的原因?(在线等待--)
- Oracle9i图形化工具是哪项呀(指管理用户和表数据的工具)
- 关于ora-12154的错误????
- 两个时间怎么比较相差多少年或多少月或多少日
- 大家随便聊!
- 大家好,我想问一个两个oracle数据库通讯的问题
- ORA-01008: not all variables bound(参数值为null)
SELECT A.C1,A.C2,B.C3 FROM
(SELECT C1,LTRIM(
MAX
(SYS_CONNECT_BY_PATH(C2,'||')),'||') C2
FROM
(
SELECT C1,C2,MIN(C2) OVER(PARTITION BY C1) C2_MIN,
(ROW_NUMBER() OVER(ORDER BY C1,C2))+(DENSE_RANK() OVER (ORDER BY C1)) NUMID
FROM (
SELECT DISTINCT C1,C2 from T
)
)
START WITH C2=C2_MIN
CONNECT BY PRIOR numid=NUMID-3
GROUP BY C1 ) A,(SELECT C1,LTRIM(
MAX
(SYS_CONNECT_BY_PATH(C3,'||')),'||') C3
FROM
(
SELECT C1,C3,MIN(C3) OVER(PARTITION BY C1) C3_MIN,
(ROW_NUMBER() OVER(ORDER BY C1,C3))+(DENSE_RANK() OVER (ORDER BY C1)) NUMID
FROM (
SELECT DISTINCT C1,C3 from T
)
)
START WITH C3=C3_MIN
CONNECT BY PRIOR numid=NUMID-3
GROUP BY C1) B
WHERE A.C1=B.C1
10g: select get_request_line_no, wagon_no, wmsys.wm_concat(storage_no)
from you_tab
group by get_request_line_no, wagon_no
9i可用自定义聚集函数
--------------------------------------------------------------------
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
*/