sql: Creating a Delimited List from Table Rows

mysql 8.0

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),
    (7499, 'ALLEN', 'SALESMAN', 7698, '1981-02-20', 1600, 300, 30),
    (7521, 'WARD', 'SALESMAN', 7698, '1981-02-22', 1250, 500, 30),
    (7566, 'JONES', 'MANAGER', 7839, '1981-04-02', 2975, NULL, 20),
    (7654, 'MARTIN', 'SALESMAN', 7698, '1981-09-28', 1250, 1400, 30),
    (7698, 'BLAKE', 'MANAGER', 7839, '1981-05-01', 2850, NULL, 30),
    (7782, 'CLARK', 'MANAGER', 7839, '1981-06-09', 2450, NULL, 10),
    (7788, 'SCOTT', 'ANALYST', 7566, '1982-12-09', 3000, NULL, 20),
    (7839, 'KING', 'PRESIDENT', NULL, '1981-11-17', 5000, NULL, 10),
    (7844, 'TURNER', 'SALESMAN', 7698, '1981-09-08', 1500, 0, 30),
    (7876, 'ADAMS', 'CLERK', 7788, '1983-01-12', 1100, NULL, 20),
    (7900, 'JAMES', 'CLERK', 7698, '1981-12-03', 950, NULL, 30),
    (7902, 'FORD', 'ANALYST', 7566, '1981-12-03', 3000, NULL, 20),
    (7934, 'MILLER', 'CLERK', 7782, '1982-01-23', 1300, NULL, 10);
  
CREATE TABLE DEPT (
    DEPTNO INTEGER,
    DNAME VARCHAR(14),
    LOC VARCHAR(13)
);
  
INSERT INTO DEPT VALUES
    (10, 'ACCOUNTING', 'NEW YORK'),
    (20, 'RESEARCH', 'DALLAS'),
    (30, 'SALES', 'CHICAGO'),
    (40, 'OPERATIONS', 'BOSTON');
  
 
-- 根据表中的行创建分隔列表
select deptno, group_concat(ename order by empno separator ',') as emps
 from emp
 group by deptno;

postgreql 16:

CREATE TABLE EMP (
    EMPNO SERIAL PRIMARY KEY,
    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, DEFAULT, 20),
    (7499, 'ALLEN', 'SALESMAN', 7698, '1981-02-20', 1600, 300, 30),
    (7521, 'WARD', 'SALESMAN', 7698, '1981-02-22', 1250, 500, 30),
    (7566, 'JONES', 'MANAGER', 7839, '1981-04-02', 2975, DEFAULT, 20),
    (7654, 'MARTIN', 'SALESMAN', 7698, '1981-09-28', 1250, 1400, 30),
    (7698, 'BLAKE', 'MANAGER', 7839, '1981-05-01', 2850, DEFAULT, 30),
    (7782, 'CLARK', 'MANAGER', 7839, '1981-06-09', 2450, DEFAULT, 10),
    (7788, 'SCOTT', 'ANALYST', 7566, '1982-12-09', 3000, DEFAULT, 20),
    (7839, 'KING', 'PRESIDENT', DEFAULT, '1981-11-17', 5000, DEFAULT, 10),
    (7844, 'TURNER', 'SALESMAN', 7698, '1981-09-08', 1500, 0, 30),
    (7876, 'ADAMS', 'CLERK', 7788, '1983-01-12', 1100, DEFAULT, 20),
    (7900, 'JAMES', 'CLERK', 7698, '1981-12-03', 950, DEFAULT, 30),
    (7902, 'FORD', 'ANALYST', 7566, '1981-12-03', 3000, DEFAULT, 20),
    (7934, 'MILLER', 'CLERK', 7782, '1982-01-23', 1300, DEFAULT, 10);
  
CREATE TABLE DEPT (
    DEPTNO INTEGER,
    DNAME VARCHAR(14),
    LOC VARCHAR(13)
);
  
INSERT INTO DEPT VALUES
    (10, 'ACCOUNTING', 'NEW YORK'),
    (20, 'RESEARCH', 'DALLAS'),
    (30, 'SALES', 'CHICAGO'),
    (40, 'OPERATIONS', 'BOSTON');
 
select * from EMP;
 
 
-- 根据表中的行创建分隔列表
select
  deptno,
  string_agg(ename, ',' order by empno) as emps  -- 修正：先分隔符，再ORDER BY
from emp
group by deptno;

sql sever 2019:

CREATE TABLE EMP (
    EMPNO int PRIMARY KEY,
    ENAME VARCHAR(10),
    JOB VARCHAR(9),
    MGR int,
    HIREDATE DATE,
    SAL int,
    COMM int,
    DEPTNO int
);
  
INSERT INTO EMP VALUES
    (7369, 'SMITH', 'CLERK', 7902, '1980-12-17', 800, DEFAULT, 20),
    (7499, 'ALLEN', 'SALESMAN', 7698, '1981-02-20', 1600, 300, 30),
    (7521, 'WARD', 'SALESMAN', 7698, '1981-02-22', 1250, 500, 30),
    (7566, 'JONES', 'MANAGER', 7839, '1981-04-02', 2975, DEFAULT, 20),
    (7654, 'MARTIN', 'SALESMAN', 7698, '1981-09-28', 1250, 1400, 30),
    (7698, 'BLAKE', 'MANAGER', 7839, '1981-05-01', 2850, DEFAULT, 30),
    (7782, 'CLARK', 'MANAGER', 7839, '1981-06-09', 2450, DEFAULT, 10),
    (7788, 'SCOTT', 'ANALYST', 7566, '1982-12-09', 3000, DEFAULT, 20),
    (7839, 'KING', 'PRESIDENT', DEFAULT, '1981-11-17', 5000, DEFAULT, 10),
    (7844, 'TURNER', 'SALESMAN', 7698, '1981-09-08', 1500, 0, 30),
    (7876, 'ADAMS', 'CLERK', 7788, '1983-01-12', 1100, DEFAULT, 20),
    (7900, 'JAMES', 'CLERK', 7698, '1981-12-03', 950, DEFAULT, 30),
    (7902, 'FORD', 'ANALYST', 7566, '1981-12-03', 3000, DEFAULT, 20),
    (7934, 'MILLER', 'CLERK', 7782, '1982-01-23', 1300, DEFAULT, 10);
  
CREATE TABLE DEPT (
    DEPTNO int,
    DNAME VARCHAR(14),
    LOC VARCHAR(13)
);
  
INSERT INTO DEPT VALUES
    (10, 'ACCOUNTING', 'NEW YORK'),
    (20, 'RESEARCH', 'DALLAS'),
    (30, 'SALES', 'CHICAGO'),
    (40, 'OPERATIONS', 'BOSTON');
 
select * from EMP;
 
-- 根据表中的行创建分隔列表
select
  deptno,
  STRING_AGG(ename, ',') WITHIN GROUP (ORDER BY empno) as emps  -- 核心修正：排序逻辑放WITHIN GROUP内
from emp
group by deptno;

oracel 21c:

-- 1. 创建 EMP 表：给 COMM 加 DEFAULT 0，日期字段用 DATE 类型（Oracle 原生支持）
CREATE TABLE EMP (
    EMPNO INT PRIMARY KEY,          -- INT 在 Oracle 中是 NUMBER(38) 的别名，没问题
    ENAME VARCHAR2(10),             -- 推荐用 VARCHAR2（Oracle 官方推荐，兼容性更好）
    JOB VARCHAR2(9),
    MGR INT,
    HIREDATE DATE,                  -- Oracle 日期类型为 DATE，无需额外定义
    SAL INT,
    COMM INT DEFAULT 0,             -- 关键修正：给 COMM 加默认值 0，支持插入时用 DEFAULT
    DEPTNO INT
);
  
-- 2. 插入 EMP 数据：日期用 TO_DATE 显式转换格式，COMM 可写 DEFAULT 或直接用 0
INSERT ALL
    INTO EMP (EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO)
    VALUES (7369, 'SMITH', 'CLERK', 7902, TO_DATE('1980-12-17', 'YYYY-MM-DD'), 800, DEFAULT, 20)
     
    INTO EMP (EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO)
    VALUES (7499, 'ALLEN', 'SALESMAN', 7698, TO_DATE('1981-02-20', 'YYYY-MM-DD'), 1600, 300, 30)
     
    INTO EMP (EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO)
    VALUES (7521, 'WARD', 'SALESMAN', 7698, TO_DATE('1981-02-22', 'YYYY-MM-DD'), 1250, 500, 30)
     
    INTO EMP (EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO)
    VALUES (7566, 'JONES', 'MANAGER', 7839, TO_DATE('1981-04-02', 'YYYY-MM-DD'), 2975, DEFAULT, 20)
     
    INTO EMP (EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO)
    VALUES (7654, 'MARTIN', 'SALESMAN', 7698, TO_DATE('1981-09-28', 'YYYY-MM-DD'), 1250, 1400, 30)
     
    INTO EMP (EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO)
    VALUES (7698, 'BLAKE', 'MANAGER', 7839, TO_DATE('1981-05-01', 'YYYY-MM-DD'), 2850, DEFAULT, 30)
     
    INTO EMP (EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO)
    VALUES (7782, 'CLARK', 'MANAGER', 7839, TO_DATE('1981-06-09', 'YYYY-MM-DD'), 2450, DEFAULT, 10)
     
    INTO EMP (EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO)
    VALUES (7788, 'SCOTT', 'ANALYST', 7566, TO_DATE('1982-12-09', 'YYYY-MM-DD'), 3000, DEFAULT, 20)
     
    INTO EMP (EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO)
    VALUES (7839, 'KING', 'PRESIDENT', DEFAULT, TO_DATE('1981-11-17', 'YYYY-MM-DD'), 5000, DEFAULT, 10)
     
    INTO EMP (EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO)
    VALUES (7844, 'TURNER', 'SALESMAN', 7698, TO_DATE('1981-09-08', 'YYYY-MM-DD'), 1500, 0, 30)
     
    INTO EMP (EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO)
    VALUES (7876, 'ADAMS', 'CLERK', 7788, TO_DATE('1983-01-12', 'YYYY-MM-DD'), 1100, DEFAULT, 20)
     
    INTO EMP (EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO)
    VALUES (7900, 'JAMES', 'CLERK', 7698, TO_DATE('1981-12-03', 'YYYY-MM-DD'), 950, DEFAULT, 30)
     
    INTO EMP (EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO)
    VALUES (7902, 'FORD', 'ANALYST', 7566, TO_DATE('1981-12-03', 'YYYY-MM-DD'), 3000, DEFAULT, 20)
     
    INTO EMP (EMPNO, ENAME, JOB, MGR, HIREDATE, SAL, COMM, DEPTNO)
    VALUES (7934, 'MILLER', 'CLERK', 7782, TO_DATE('1982-01-23', 'YYYY-MM-DD'), 1300, DEFAULT, 10)
SELECT 1 FROM DUAL; -- 必须添加此句，Oracle 要求 INSERT ALL 以子查询结束
 
 
  
-- 3. 创建 DEPT 表：给 DEPTNO 加 PRIMARY KEY，保证唯一性和关联能力
CREATE TABLE DEPT (
    DEPTNO INT PRIMARY KEY,         -- 关键修正：DEPTNO 设为主键
    DNAME VARCHAR2(14),
    LOC VARCHAR2(13)
);
  
-- 4. 插入 DEPT 数据（无语法问题，直接使用）
INSERT ALL
INTO DEPT VALUES
    (10, 'ACCOUNTING', 'NEW YORK')
INTO DEPT VALUES    (20, 'RESEARCH', 'DALLAS')
INTO DEPT VALUES    (30, 'SALES', 'CHICAGO')
 INTO DEPT VALUES   (40, 'OPERATIONS', 'BOSTON')   
SELECT 1 FROM DUAL;
-- 最后提交事务（Oracle 默认手动提交，需显式执行）
 
 
select * from EMP;
 
select * from DEPT;
 
-- 根据表中的行创建分隔列表
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;