表FID PID PNAME
1 1 'aa'
2 1 'bb'
3 2 'cc'
4 2 'dd'
5 2 'ee'
6 3 'ff'需要的结果如下;
PID PNAMES
1 ‘aabb'
2 'ccddee'
3 'ff'
1 1 'aa'
2 1 'bb'
3 2 'cc'
4 2 'dd'
5 2 'ee'
6 3 'ff'需要的结果如下;
PID PNAMES
1 ‘aabb'
2 'ccddee'
3 'ff'
解决方案 »
- oracle 表分区恢复的问题
- oracle用户和数据库的问题
- 关于使用tera term工具访问Linux下的oracle数据库的问题!
- 请教一个查询语句:未知表结构除了已知一个字段。查询除掉这个字段的所有纪录。
- 表里可以建布尔类型的字段马?
- oraclestarORCL及TNSlistener不能启动
- 高分求解:我想从一个oracle表中读第n行到第M行的数据,应该怎么写SQL
- 调用一个ORACLE的存储过程
- 一个select语句的组合请教!!
- 关于 Truncate Table后,索引占的空间的问题
- 在oracle存储过程中判断是否有这张表,没有就插入一张表有就显示删除\还是跳过。的列子啊
- 在安装oracle10g的时候总是出现EnterpriseManager配置失败的错误
建议利用过程或者函数来实现对应功能。
2 (
3 SELECT 1 AS FID, 1 AS PID, 'aa' AS PNAME FROM DUAL UNION
4 SELECT 2 AS FID, 1 AS PID, 'bb' AS PNAME FROM DUAL UNION
5 SELECT 3 AS FID, 2 AS PID, 'cc' AS PNAME FROM DUAL UNION
6 SELECT 4 AS FID, 2 AS PID, 'dd' AS PNAME FROM DUAL UNION
7 SELECT 5 AS FID, 2 AS PID, 'ee' AS PNAME FROM DUAL UNION
8 SELECT 6 AS FID, 3 AS PID, 'ff' AS PNAME FROM DUAL
9 )
10 SELECT pid,REPLACE(WMSYS.WM_CONCAT(PNAME),',','')
11 FROM test
12 GROUP BY PID;
PID REPLACE(WMSYS.WM_CONCAT(PNAME)
---------- --------------------------------------------------------------------------------
1 aabb
2 cceedd
3 ff
SQL>
2 (
3 SELECT 1 AS FID, 1 AS PID, 'aa' AS PNAME FROM DUAL UNION
4 SELECT 2 AS FID, 1 AS PID, 'bb' AS PNAME FROM DUAL UNION
5 SELECT 3 AS FID, 2 AS PID, 'cc' AS PNAME FROM DUAL UNION
6 SELECT 4 AS FID, 2 AS PID, 'dd' AS PNAME FROM DUAL UNION
7 SELECT 5 AS FID, 2 AS PID, 'ee' AS PNAME FROM DUAL UNION
8 SELECT 6 AS FID, 3 AS PID, 'ff' AS PNAME FROM DUAL
9 )
10 SELECT DISTINCT pid, REPLACE(wmsys.wm_concat(pname)over (PARTITION BY pid ),',','') AS C_PNAME
11 FROM
12 TEST
13 ORDER BY PID
14 ;
PID C_PNAME
---------- --------------------------------------------------------------------------------
1 aabb
2 ccddee
3 ff