Dear team,
I'm having some troubles trying to using the connect_by_root operator and pivot tables in this example below:
so we got the table:
CREATE TABLE Cats (
name VARCHAR2(15) CONSTRAINT cat_name_nn NOT NULL,
gender VARCHAR2(1) CONSTRAINT cat_gen_ch CHECK (gender IN('M', 'W')),
nickname VARCHAR2(15) CONSTRAINT cat_pk PRIMARY KEY,
function VARCHAR2(10),
chief VARCHAR2(15),
in_herd_since DATE DEFAULT SYSDATE CONSTRAINT cat_inherd_nn NOT NULL,
mice_ration NUMBER(3),
mice_extra NUMBER(3),
band_no NUMBER(2)
);
INSERT INTO Cats VALUES ('JACEK','M','CAKE','CATCHING','BALD',TO_DATE('2008-12-01', 'YYYY-MM-DD'),67,NULL,2);
INSERT INTO Cats VALUES ('BARI','M','TUBE','CATCHER','BALD',TO_DATE('2009-09-01', 'YYYY-MM-DD'),56,NULL,2);
INSERT INTO Cats VALUES ('MICKA','W','LOLA','NICE','TIGER',TO_DATE('2009-10-14', 'YYYY-MM-DD'),25,47,1);
INSERT INTO Cats VALUES ('LUCEK','M','ZERO','CAT','HEN',TO_DATE('2010-03-01', 'YYYY-MM-DD'),43,NULL,3);
INSERT INTO Cats VALUES ('SONIA','W','FLUFFY','NICE','ZOMBIES',TO_DATE('2010-11-18', 'YYYY-MM-DD'),20,35,3);
INSERT INTO Cats VALUES ('LATKA','W','EAR','CAT','REEF',TO_DATE('2011-01-01', 'YYYY-MM-DD'),40,NULL,4);
INSERT INTO Cats VALUES ('DUDEK','M','SMALL','CAT','REEF',TO_DATE('2011-05-15', 'YYYY-MM-DD'),40,NULL,4);
INSERT INTO Cats VALUES ('MRUCZEK','M','TIGER','BOSS',NULL,TO_DATE('2002-01-01', 'YYYY-MM-DD'),103,33,1);
INSERT INTO Cats VALUES ('CHYTRY','M','BOLEK','DIVISIVE','TIGER',TO_DATE('2002-05-05', 'YYYY-MM-DD'),50,NULL,1);
INSERT INTO Cats VALUES ('KOREK','M','ZOMBIES','THUG','TIGER',TO_DATE('2004-03-16', 'YYYY-MM-DD'),75,13,3);
INSERT INTO Cats VALUES ('BOLEK','M','BALD','THUG','TIGER',TO_DATE('2006-08-15', 'YYYY-MM-DD'),72,21,2);
INSERT INTO Cats VALUES ('ZUZIA','W','FAST','CATCHING','BALD',TO_DATE('2006-07-21', 'YYYY-MM-DD'),65,NULL,2);
INSERT INTO Cats VALUES ('RUDA','W','LITTLE','NICE','TIGER',TO_DATE('2006-09-17', 'YYYY-MM-DD'),22,42,1);
INSERT INTO Cats VALUES ('PUCEK','M','REEF','CATCHING','TIGER',TO_DATE('2006-10-15', 'YYYY-MM-DD'),65,NULL,4);
INSERT INTO Cats VALUES ('PUNIA','W','HEN','CATCHING','ZOMBIES',TO_DATE('2008-01-01', 'YYYY-MM-DD'),61,NULL,3);
INSERT INTO Cats VALUES ('BELA','W','MISS','NICE','BALD',TO_DATE('2008-02-01', 'YYYY-MM-DD'),24,28,2);
INSERT INTO Cats VALUES ('KSAWERY','M','MAN','CATCHER','REEF',TO_DATE('2008-07-12', 'YYYY-MM-DD'),51,NULL,4);
INSERT INTO Cats VALUES ('MELA','W','LADY','CATCHER','REEF',TO_DATE('2008-11-01', 'YYYY-MM-DD'),51,NULL,4);
What I'm trying to do is for cats with function CAT and NICE, display the names of all their chiefs in order compatible of their hierarchy, I thought it thro and thought it was just fine, but isn't working:
SELECT * FROM
(SELECT CONNECT_BY_ROOT name, function, name
from Cats
CONNECT BY PRIOR chief = nickname
)
PIVOT (
COUNT(*) cnt
FOR
LEVEL IN (2 "Chief 1" , 3 "Chief 2", 4 "Chief 3"))
WHERE function IN ('CAT', 'NICE');
What I'm trying to get is this:
Name Function Chief 1 Chief 2 Chief 3
---------- --- ------------- ---------- --- ---------- --- ---------- ---
LUCEK | CAT | PUNIA | KOREK | MRUCZEK
RUDA | NICE | MRUCZEK
MICKA | NICE | MRUCZEK
SONIA | NICE | KOREK | MRUCZEK
BELA | NICE | BOLEK | MRUCZEK
DUDEK | CAT | PUCEK | MRUCZEK
LATKA | CAT | PUCEK | MRUCZEK
Kindly give me some hints or tips please.
Regards,
Miguel