如何把一个Group的信息归总到一列上.

EMP

 

ID      NAME          DEPTNO

------- -------------- --------

1       张三             1

2       李四             1

3       王五             1

4       Dany            2

5       Leo              2

 

结果

 

DEPTNO  NAME

-------      ----------------------------------------

1             张三,李四,王五

2             Dany,Leo

 

如果你当前使用的DBMS是Oracle.

可以用Oracle的内置函数SYS_CONNECT_BY_PATH来构建分割列表.

 

SELECT  DEPTNO,

              LTRIM(SYS_CONNECT_BY_PATH(NAME, ','), ',') NAME

    FROM  (

SELECT  DEPTNO,

              NAME,

              ROW_NUMBER() OVER (PARTITION BY DEPTNO ORDER BY ID) RN,

              COUNT(*) OVER (PARTITION  BY DEPTNO) CNT

  FROM  EMP )

 WHERE  LEVEL  = CNT

  START WITH RN = 1

CONNECT BY PRIOR DEPTNO = DEPTNO AND PRIOR RN = RN - 1

 

具体说明待再补.