求救sql语句写法，数据库是oracle

Question

如下格式的表得数据
发送人      标示号   名称   接收人
li         0001    name1  wang
li         0001    name1  zhang
li         0001    name1  cai
zhao       0002    name2  wang
zhao       0003    name2  qin上表字段，在组内接收人不同，其他都相同。能否有sql转成如下形式：
li         0001    name1  wang,zhang,cai
zhao       0002    name2  wang,qin多谢！

Answer 1

oracle 10g:
SELECT D.发送人,
       D.标示号,
       D.名称,
       WMSYS.WM_CONCAT(接收人)  接收人
  FROM yourTable D
GROUP BY D.发送人, D.标示号,D.名称;

Answer 2

10G的看楼上，
10G以下的，参考此贴,用sys_connect_by_path来实现
http://topic.csdn.net/u/20080505/11/a0958b42-d938-465f-972a-0f61a2969c97.html?seed=491226048

Answer 3

SQL> select * from tbl;SENDER                                                       FLAGNUM                                                      NAME                                                         RECIVER
------------------------------------------------------------ ------------------------------------------------------------ ------------------------------------------------------------ ------------------------------------------------------------
li                                                           0001                                                         name1                                                        wang
li                                                           0001                                                         name1                                                        zhang
li                                                           0001                                                         name1                                                        cai
zhao                                                         0002                                                         name2                                                        wang
zhao                                                         0002                                                         name2                                                        qinSQL> 
SQL> SELECT sender,flagnum,name,
  2  MAX(decode(rn, 1, reciver, NULL)) ||
  3  MAX(decode(rn, 2, ',' || reciver, NULL)) ||
  4  MAX(decode(rn, 3, ',' || reciver, NULL)) str
  5  FROM (SELECT sender,
  6  reciver,flagnum,name,
  7  row_number() over(PARTITION BY sender,flagnum,name ORDER BY reciver) AS rn
  8  FROM tbl) t
  9  GROUP BY sender,flagnum,name
 10  ORDER BY 1;SENDER                                                       FLAGNUM                                                      NAME                                                         STR
------------------------------------------------------------ ------------------------------------------------------------ ------------------------------------------------------------ --------------------------------------------------------------------------------
li                                                           0001                                                         name1                                                        cai,wang,zhang
zhao                                                         0002                                                         name2                                                        qin,wang

Answer 4

很多問行轉列或列轉行的。ORACLE是否考慮加兩個這樣的函數了。 ^_^