LISTAGG

You must execute the code again to save those values in the database.

-- 建立訂單主表
CREATE TABLE ORDERS (
  ORDER_ID     VARCHAR2(10),  -- 訂單編號
  CUSTOMER_ID  VARCHAR2(10),  -- 顧客編號
  ORDER_DATE   DATE           -- 訂單日期
);

-- 建立訂單明細表
CREATE TABLE ORDER_ITEMS (
  ORDER_ID      VARCHAR2(10),  -- 對應的訂單編號
  PRODUCT_ID    VARCHAR2(10),  -- 商品ID
  PRODUCT_NAME  VARCHAR2(100)  -- 商品名稱
);

-- 插入訂單資料
INSERT INTO ORDERS VALUES ('1001', 'C001', TO_DATE('2025-01-01', 'YYYY-MM-DD'));
INSERT INTO ORDERS VALUES ('1002', 'C002', TO_DATE('2025-01-11', 'YYYY-MM-DD'));
INSERT INTO ORDERS VALUES ('1003', 'C003', TO_DATE('2025-02-02', 'YYYY-MM-DD'));
INSERT INTO ORDERS VALUES ('1004', 'C004', TO_DATE('2025-03-03', 'YYYY-MM-DD'));
INSERT INTO ORDERS VALUES ('1005', 'C005', TO_DATE('2025-03-23', 'YYYY-MM-DD'));
INSERT INTO ORDERS VALUES ('1006', 'C002', TO_DATE('2025-02-22', 'YYYY-MM-DD'));

-- 插入訂單品項資料
INSERT INTO ORDER_ITEMS VALUES ('1001', 'P001', 'Camera');
INSERT INTO ORDER_ITEMS VALUES ('1001', 'P002', 'Tripod');
INSERT INTO ORDER_ITEMS VALUES ('1002', 'P003', 'Laptop');
INSERT INTO ORDER_ITEMS VALUES ('1003', 'P001', 'Camera');
INSERT INTO ORDER_ITEMS VALUES ('1003', 'P003', 'Laptop');
INSERT INTO ORDER_ITEMS VALUES ('1004', 'P004', 'Coffee');
INSERT INTO ORDER_ITEMS VALUES ('1005', 'P005', 'Bicycle');
INSERT INTO ORDER_ITEMS VALUES ('1006', 'P006', 'DIY Kit');

-- 提交資料異動
COMMIT;

SELECT 
  CUSTOMER_ID, 
  LISTAGG(OI.PRODUCT_NAME, ', ') WITHIN GROUP (ORDER BY OI.PRODUCT_ID) AS PURCHASED_PRODUCTS
FROM ORDERS O
JOIN ORDER_ITEMS OI ON O.ORDER_ID = OI.ORDER_ID
GROUP BY CUSTOMER_ID;

CUSTOMER_I	PURCHASED_PRODUCTS
C001	Camera, Tripod
C002	Laptop, DIY Kit
C003	Camera, Laptop
C004	Coffee
C005	Bicycle

CUSTOMER_I	PURCHASED_PRODUCTS
C001	Camera, Tripod
C002	Laptop, DIY Kit
C003	Camera, Laptop
C004	Coffee
C005	Bicycle