Oracle SQL列出key所有的values

-- 0. Create demo table
CREATE TABLE test (
PROD_CAT VARCHAR2(10),
PROD_NO VARCHAR2(15)
);

-- 1. Create demo data
BEGIN
INSERT INTO test (PROD_CAT,PROD_NO) VALUES ('A','1');
INSERT INTO test (PROD_CAT,PROD_NO) VALUES ('A','2');
INSERT INTO test (PROD_CAT,PROD_NO) VALUES ('A','3');
INSERT INTO test (PROD_CAT,PROD_NO) VALUES ('B','2');
INSERT INTO test (PROD_CAT,PROD_NO) VALUES ('A','4');
INSERT INTO test (PROD_CAT,PROD_NO) VALUES ('B','1');
INSERT INTO test (PROD_CAT,PROD_NO) VALUES ('A','6');
INSERT INTO test (PROD_CAT,PROD_NO) VALUES ('A','7');
INSERT INTO test (PROD_CAT,PROD_NO) VALUES ('B','5');
INSERT INTO test (PROD_CAT,PROD_NO) VALUES ('B','8');
INSERT INTO test (PROD_CAT,PROD_NO) VALUES ('C','0');
INSERT INTO test (PROD_CAT,PROD_NO) VALUES ('D','1');
INSERT INTO test (PROD_CAT,PROD_NO) VALUES ('C','2');
INSERT INTO test (PROD_CAT,PROD_NO) VALUES ('D','3');
INSERT INTO test (PROD_CAT,PROD_NO) VALUES ('D','7');
COMMIT;
END;

-- 2. Use Oracle Analytic Function and Hierarchical Queries
SELECT PROD_CAT, max(substr(PROD_NO, 2)) PROD_NO
  FROM (SELECT PROD_CAT, sys_connect_by_path(PROD_NO, ',') PROD_NO
          FROM (SELECT PROD_CAT,
                       PROD_NO,
                       PROD_CAT || rn rchild,
                       PROD_CAT || (rn - 1) rfather
                  FROM (SELECT test.PROD_CAT,
                               test.PROD_NO,
                               row_number() over(PARTITION BY test.PROD_CAT ORDER BY test.PROD_NO) rn
                          FROM test))
        CONNECT BY PRIOR rchild = rfather
         START WITH rfather LIKE '%0')
 GROUP BY PROD_CAT ORDER BY 1;