1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37
|
CREATE TABLE TMP_PARENT ("NOM_PARENT" VARCHAR2(20), "ID_ENFANT" NUMBER(10,0),"POPULATION" NUMBER(10,0));
INSERT INTO TMP_PARENT (NOM_PARENT,ID_ENFANT,POPULATION) VALUES ('parent1',1,10);
INSERT INTO TMP_PARENT (NOM_PARENT,ID_ENFANT,POPULATION) VALUES ('parent1',2,2);
INSERT INTO TMP_PARENT (NOM_PARENT,ID_ENFANT,POPULATION) VALUES ('parent1',3,1);
INSERT INTO TMP_PARENT (NOM_PARENT,ID_ENFANT,POPULATION) VALUES ('parent2',4,3);
CREATE TABLE TMP_ENFANT ("ID_ENFANT" NUMBER(10,0), "NOM_ENFANT" VARCHAR2(20));
INSERT INTO TMP_ENFANT (ID_ENFANT,NOM_ENFANT) VALUES (1,'enfant1');
INSERT INTO TMP_ENFANT (ID_ENFANT,NOM_ENFANT) VALUES (2,'enfant2');
INSERT INTO TMP_ENFANT (ID_ENFANT,NOM_ENFANT) VALUES (3,'enfant3');
INSERT INTO TMP_ENFANT (ID_ENFANT,NOM_ENFANT) VALUES (4,'enfant4');
WITH
FLT_DATA AS (
SELECT DISTINCT TMP_PARENT.NOM_PARENT,TMP_ENFANT.NOM_ENFANT
FROM TMP_PARENT
LEFT JOIN TMP_ENFANT ON TMP_PARENT.ID_ENFANT=TMP_ENFANT.ID_ENFANT
),
FLT_JOIN AS (
SELECT NOM_PARENT,LTRIM(SYS_CONNECT_BY_PATH(NOM_ENFANT,','),',') NOM_ENFANTS
FROM (
SELECT
NOM_PARENT
,NOM_ENFANT
,ROW_NUMBER() OVER (PARTITION BY NOM_PARENT ORDER BY NOM_ENFANT) rn
,COUNT(*) OVER (PARTITION BY NOM_PARENT) cnt
FROM FLT_DATA
)
WHERE rn=cnt
START WITH rn=1
CONNECT BY PRIOR NOM_PARENT = NOM_PARENT AND PRIOR rn = rn-1
)
SELECT FLT_JOIN.NOM_PARENT,FLT_JOIN.NOM_ENFANTS,SUM(POPULATION)
FROM FLT_JOIN
JOIN TMP_PARENT ON FLT_JOIN.NOM_PARENT=TMP_PARENT.NOM_PARENT
GROUP BY FLT_JOIN.NOM_PARENT,FLT_JOIN.NOM_ENFANTS; |
Partager