表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连接问题
- 9i,建立资料库的时候,是在原有数据库上建?还是新建?我建了两个库了,一个oradb,一个mydb???
- 关于索引的一个菜鸟问题
- 怎样把 developer6i和oracle9i
- SQL语句中自定义函数应用问题
- 急问!
- sql语句的最大长度。
- 请教sql高手:动态港存的计算问题 (100分)
- 100分相问如何在windows xp 上安装oracle8.1.5?
- 在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