CREATE TABLE EMP
       (EMPNO integer NOT NULL,
        ENAME VARCHAR(10),
        JOB VARCHAR(9),
        MGR integer,
        HIREDATE DATE,
        SAL integer,
        COMM integer,
        DEPTNO integer);

INSERT INTO EMP values (7369, 'SMITH',  'CLERK',     7902, '1980-12-17',  800,  NULL, 20);
INSERT INTO EMP values (7499, 'ALLEN',  'SALESMAN',  7698, '1981-2-20',   1600, 300,  30);
INSERT INTO EMP VALUES (7521, 'WARD',   'SALESMAN',  7698, '1981-2-22',   1250, 500,  30);
INSERT INTO EMP VALUES (7566, 'JONES',  'MANAGER',   7839, '1981-4-2',    2975, NULL, 20);
INSERT INTO EMP VALUES (7654, 'MARTIN', 'SALESMAN',  7698, '1981-9-28',   1250, 1400, 30);
INSERT INTO EMP VALUES (7698, 'BLAKE',  'MANAGER',   7839, '1981-5-1',    2850, NULL, 30);
INSERT INTO EMP VALUES (7782, 'CLARK',  'MANAGER',   7839, '1981-6-9',    2450, NULL, 10);
INSERT INTO EMP VALUES (7788, 'SCOTT',  'ANALYST',   7566, '1982-12-9',   3000, NULL, 20);
INSERT INTO EMP VALUES (7839, 'KING',   'PRESIDENT', NULL, '1981-11-17',  5000, NULL, 10);
INSERT INTO EMP VALUES (7844, 'TURNER', 'SALESMAN',  7698, '1981-9-8',    1500,    0, 30);
INSERT INTO EMP VALUES (7876, 'ADAMS',  'CLERK',     7788, '1983-1-12',   1100, NULL, 20);
INSERT INTO EMP VALUES (7900, 'JAMES',  'CLERK',     7698, '1981-12-3',   950,  NULL, 30);
INSERT INTO EMP VALUES (7902, 'FORD',   'ANALYST',   7566, '1981-12-3',   3000, NULL, 20);
INSERT INTO EMP VALUES (7934, 'MILLER', 'CLERK',     7782, '1982-1-23',   1300, NULL, 10);

/* mysql */
select 	deptno,
		group_concat(ename order by empno separator ',') as emps
from emp
group by deptno

/* oracle 11g*/
select   deptno,
         listagg(ename, ',') within group (order by empno) as emps
from emp
group by deptno

/* oracle */
select deptno, ltrim(sys_connect_by_path(ename, ','),',') emps from (
select deptno, ename,
       row_number() over (partition by deptno order by empno) 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

--sql server
with x (deptno, empno, list, cnt, leng) as (
select deptno, empno, convert(varchar(100), ename) list, count(*) over (partition by deptno) as cnt, 1 as leng
from emp
union all
select e.deptno, e.empno, convert(varchar(100), x.list + ',' + e.ename),x.cnt,x.leng + 1
from emp e, x
where e.deptno = x.deptno
and   e.empno  > x.empno
)
select deptno, list
from x
where leng = cnt
order by deptno

--sql server 2019
select deptno, STRING_AGG(ename, ',') within group (order by ename) as list
from emp
group by deptno

select deptno, STRING_AGG(ename, ',') within group (order by ename) as list
into emp2
from emp
group by deptno

--拆分
select deptno, value
from emp2
cross apply string_split(list, ',')