sqlhsqldblibreoffice

Query for "grouping" rows


I'm trying to set up a query that would display the data in a certain way, but I can't manage it.

Tables:

CREATE TABLE T_EMPLOYEES (
    EMP_ID INTEGER IDENTITY NOT NULL,
    EMP_LASTNAME VARCHAR (32) NOT NULL,
    EMP_FIRSTNAME VARCHAR (32) NOT NULL,
    CONSTRAINT PK_EMP_ID PRIMARY KEY (EMP_ID)
);

CREATE TABLE T_BENEFICIARIES (
    BEN_ID INTEGER IDENTITY NOT NULL,
    BEN_LASTNAME VARCHAR (32) NOT NULL,
    BEN_FIRSTNAME VARCHAR (32) NOT NULL,
    CONSTRAINT PK_BEN_ID PRIMARY KEY (BEN_ID)
);

CREATE TABLE T_DAYS (
    DAY_ID INTEGER NOT NULL,
    DAY_LBL VARCHAR (10) NOT NULL,
    CONSTRAINT PK_DAY_ID PRIMARY KEY (DAY_ID)
);

CREATE TABLE T_PLANNINGS (
    PLA_ID INTEGER NOT NULL,
    CONSTRAINT PK_PLA_ID PRIMARY KEY (PLA_ID),
    CONSTRAINT FK_PLA_SAL FOREIGN KEY (PLA_ID) REFERENCES T_EMPLOYEES (EMP_ID)
);

CREATE TABLE T_SLOTS (
    SLO_ID INTEGER IDENTITY NOT NULL,
    PLA_ID INTEGER NOT NULL,
    BEN_ID INTEGER NOT NULL,
    DAY_ID INTEGER NOT NULL,
    SLO_BEG TIME NOT NULL,
    SLO_END TIME NOT NULL,
    CONSTRAINT PK_SLO_ID PRIMARY KEY (SLO_ID),
    CONSTRAINT FK_SLO_PLA FOREIGN KEY (PLA_ID) REFERENCES T_PLANNINGS (PLA_ID),
    CONSTRAINT FK_SLO_BEN FOREIGN KEY (BEN_ID) REFERENCES T_BENEFICIARIES (BEN_ID),
    CONSTRAINT FK_SLO_DAY FOREIGN KEY (DAY_ID) REFERENCES T_DAYS (DAY_ID)
);

Insert Script

INSERT INTO T_EMPLOYEES (EMP_ID, EMP_LASTNAME, EMP_FIRSTNAME) VALUES (1, 'Dermien', 'Isabelle');
INSERT INTO T_EMPLOYEES (EMP_ID, EMP_LASTNAME, EMP_FIRSTNAME) VALUES (2, 'Declercq', 'Mireille');
INSERT INTO T_EMPLOYEES (EMP_ID, EMP_LASTNAME, EMP_FIRSTNAME) VALUES (3, 'Montosier', 'Émilie');
INSERT INTO T_EMPLOYEES (EMP_ID, EMP_LASTNAME, EMP_FIRSTNAME) VALUES (4, 'Arsenault', 'Florence');
INSERT INTO T_EMPLOYEES (EMP_ID, EMP_LASTNAME, EMP_FIRSTNAME) VALUES (5, 'Texel', 'Isabelle');
INSERT INTO T_EMPLOYEES (EMP_ID, EMP_LASTNAME, EMP_FIRSTNAME) VALUES (6, 'Vassent', 'Carole');
INSERT INTO T_BENEFICIARIES (BEN_ID, BEN_LASTNAME, BEN_FIRSTNAME) VALUES (1, 'Duval', 'Sophie');
INSERT INTO T_BENEFICIARIES (BEN_ID, BEN_LASTNAME, BEN_FIRSTNAME) VALUES (2, 'Hivet', 'Marine');
INSERT INTO T_BENEFICIARIES (BEN_ID, BEN_LASTNAME, BEN_FIRSTNAME) VALUES (3, 'Fobert', 'Michelle');
INSERT INTO T_BENEFICIARIES (BEN_ID, BEN_LASTNAME, BEN_FIRSTNAME) VALUES (4, 'Merlet', 'Perrine');
INSERT INTO T_BENEFICIARIES (BEN_ID, BEN_LASTNAME, BEN_FIRSTNAME) VALUES (5, 'Loiseau', 'Thomas');
INSERT INTO T_BENEFICIARIES (BEN_ID, BEN_LASTNAME, BEN_FIRSTNAME) VALUES (6, 'Chevalier', 'Ambre');
INSERT INTO T_BENEFICIARIES (BEN_ID, BEN_LASTNAME, BEN_FIRSTNAME) VALUES (7, 'Frouin', 'Antoine');
INSERT INTO T_BENEFICIARIES (BEN_ID, BEN_LASTNAME, BEN_FIRSTNAME) VALUES (8, 'Herbin', 'Séverine');
INSERT INTO T_BENEFICIARIES (BEN_ID, BEN_LASTNAME, BEN_FIRSTNAME) VALUES (9, 'Masson', 'Danika');
INSERT INTO T_BENEFICIARIES (BEN_ID, BEN_LASTNAME, BEN_FIRSTNAME) VALUES (10, 'Gilbert', 'Anna');
INSERT INTO T_BENEFICIARIES (BEN_ID, BEN_LASTNAME, BEN_FIRSTNAME) VALUES (11, 'Chauvin', 'Béatrice');
INSERT INTO T_BENEFICIARIES (BEN_ID, BEN_LASTNAME, BEN_FIRSTNAME) VALUES (12, 'Brunet', 'Valériane');
INSERT INTO T_BENEFICIARIES (BEN_ID, BEN_LASTNAME, BEN_FIRSTNAME) VALUES (13, 'Oger', 'Lucien');
INSERT INTO T_BENEFICIARIES (BEN_ID, BEN_LASTNAME, BEN_FIRSTNAME) VALUES (14, 'Lemesle', 'Marielle');
INSERT INTO T_BENEFICIARIES (BEN_ID, BEN_LASTNAME, BEN_FIRSTNAME) VALUES (15, 'Bellanger', 'Maxime');
INSERT INTO T_BENEFICIARIES (BEN_ID, BEN_LASTNAME, BEN_FIRSTNAME) VALUES (16, 'Noblecourt', 'Alain');
INSERT INTO T_BENEFICIARIES (BEN_ID, BEN_LASTNAME, BEN_FIRSTNAME) VALUES (17, 'Delaune', 'Constance');
INSERT INTO T_BENEFICIARIES (BEN_ID, BEN_LASTNAME, BEN_FIRSTNAME) VALUES (18, 'Metayer', 'Maxence');
INSERT INTO T_BENEFICIARIES (BEN_ID, BEN_LASTNAME, BEN_FIRSTNAME) VALUES (19, 'Bedouet', 'Jules');
INSERT INTO T_BENEFICIARIES (BEN_ID, BEN_LASTNAME, BEN_FIRSTNAME) VALUES (20, 'Lecomte', 'Sylvie');
INSERT INTO T_BENEFICIARIES (BEN_ID, BEN_LASTNAME, BEN_FIRSTNAME) VALUES (21, 'Maurier', 'Gaétan');
INSERT INTO T_BENEFICIARIES (BEN_ID, BEN_LASTNAME, BEN_FIRSTNAME) VALUES (22, 'Trichet', 'Oscar');
INSERT INTO T_BENEFICIARIES (BEN_ID, BEN_LASTNAME, BEN_FIRSTNAME) VALUES (23, 'Monnier', 'Liliane');
INSERT INTO T_BENEFICIARIES (BEN_ID, BEN_LASTNAME, BEN_FIRSTNAME) VALUES (24, 'Jupin', 'William');
INSERT INTO T_BENEFICIARIES (BEN_ID, BEN_LASTNAME, BEN_FIRSTNAME) VALUES (25, 'Charrier', 'Pierre');
INSERT INTO T_BENEFICIARIES (BEN_ID, BEN_LASTNAME, BEN_FIRSTNAME) VALUES (26, 'Bourget', 'Jasmine');
INSERT INTO T_BENEFICIARIES (BEN_ID, BEN_LASTNAME, BEN_FIRSTNAME) VALUES (27, 'Dumont', 'Méline');
INSERT INTO T_BENEFICIARIES (BEN_ID, BEN_LASTNAME, BEN_FIRSTNAME) VALUES (28, 'Grimaud', 'Raymond');
INSERT INTO T_BENEFICIARIES (BEN_ID, BEN_LASTNAME, BEN_FIRSTNAME) VALUES (29, 'Lefèvre', 'Hélène');
INSERT INTO T_BENEFICIARIES (BEN_ID, BEN_LASTNAME, BEN_FIRSTNAME) VALUES (30, 'Piron', 'Victor');
INSERT INTO T_BENEFICIARIES (BEN_ID, BEN_LASTNAME, BEN_FIRSTNAME) VALUES (31, 'Blot', 'Blandine');
INSERT INTO T_BENEFICIARIES (BEN_ID, BEN_LASTNAME, BEN_FIRSTNAME) VALUES (32, 'Bourgeois', 'Béatrice');
INSERT INTO T_BENEFICIARIES (BEN_ID, BEN_LASTNAME, BEN_FIRSTNAME) VALUES (33, 'Cappe', 'Julia');
INSERT INTO T_BENEFICIARIES (BEN_ID, BEN_LASTNAME, BEN_FIRSTNAME) VALUES (34, 'Thiroux', 'Alba');
INSERT INTO T_BENEFICIARIES (BEN_ID, BEN_LASTNAME, BEN_FIRSTNAME) VALUES (35, 'Crespin', 'Fabien');
INSERT INTO T_BENEFICIARIES (BEN_ID, BEN_LASTNAME, BEN_FIRSTNAME) VALUES (36, 'Dubuc', 'Suzanne');
INSERT INTO T_BENEFICIARIES (BEN_ID, BEN_LASTNAME, BEN_FIRSTNAME) VALUES (37, 'Venant', 'Achille');
INSERT INTO T_BENEFICIARIES (BEN_ID, BEN_LASTNAME, BEN_FIRSTNAME) VALUES (38, 'Couty', 'Clara');
INSERT INTO T_BENEFICIARIES (BEN_ID, BEN_LASTNAME, BEN_FIRSTNAME) VALUES (39, 'Le Gall', 'Marc');
INSERT INTO T_BENEFICIARIES (BEN_ID, BEN_LASTNAME, BEN_FIRSTNAME) VALUES (40, 'Clichet', 'Angélique');
INSERT INTO T_BENEFICIARIES (BEN_ID, BEN_LASTNAME, BEN_FIRSTNAME) VALUES (41, 'Didiot', 'Nina');
INSERT INTO T_BENEFICIARIES (BEN_ID, BEN_LASTNAME, BEN_FIRSTNAME) VALUES (42, 'Poidevin', 'Laure');
INSERT INTO T_BENEFICIARIES (BEN_ID, BEN_LASTNAME, BEN_FIRSTNAME) VALUES (43, 'Barbier', 'Michael');
INSERT INTO T_BENEFICIARIES (BEN_ID, BEN_LASTNAME, BEN_FIRSTNAME) VALUES (44, 'Monthuit', 'Laurence');
INSERT INTO T_BENEFICIARIES (BEN_ID, BEN_LASTNAME, BEN_FIRSTNAME) VALUES (45, 'Godard', 'Joy');
INSERT INTO T_BENEFICIARIES (BEN_ID, BEN_LASTNAME, BEN_FIRSTNAME) VALUES (46, 'Haupa', 'Alain');
INSERT INTO T_BENEFICIARIES (BEN_ID, BEN_LASTNAME, BEN_FIRSTNAME) VALUES (47, 'Gobeaux', 'Charlie');
INSERT INTO T_BENEFICIARIES (BEN_ID, BEN_LASTNAME, BEN_FIRSTNAME) VALUES (48, 'Gaillet', 'Mireille');
INSERT INTO T_BENEFICIARIES (BEN_ID, BEN_LASTNAME, BEN_FIRSTNAME) VALUES (49, 'Jacob', 'Sylvia');
INSERT INTO T_BENEFICIARIES (BEN_ID, BEN_LASTNAME, BEN_FIRSTNAME) VALUES (50, 'Lenoir', 'Francis');
INSERT INTO T_BENEFICIARIES (BEN_ID, BEN_LASTNAME, BEN_FIRSTNAME) VALUES (51, 'Courtois', 'Anthony');
INSERT INTO T_BENEFICIARIES (BEN_ID, BEN_LASTNAME, BEN_FIRSTNAME) VALUES (52, 'Maillet', 'Gabriel');
INSERT INTO T_BENEFICIARIES (BEN_ID, BEN_LASTNAME, BEN_FIRSTNAME) VALUES (53, 'Migeaux', 'Héloïse');
INSERT INTO T_BENEFICIARIES (BEN_ID, BEN_LASTNAME, BEN_FIRSTNAME) VALUES (54, 'Hainault', 'Lucas');
INSERT INTO T_BENEFICIARIES (BEN_ID, BEN_LASTNAME, BEN_FIRSTNAME) VALUES (55, 'Cochet', 'Jules');
INSERT INTO T_BENEFICIARIES (BEN_ID, BEN_LASTNAME, BEN_FIRSTNAME) VALUES (56, 'Lefèvre', 'Murielle');
INSERT INTO T_BENEFICIARIES (BEN_ID, BEN_LASTNAME, BEN_FIRSTNAME) VALUES (57, 'Bousquet', 'Éva');
INSERT INTO T_BENEFICIARIES (BEN_ID, BEN_LASTNAME, BEN_FIRSTNAME) VALUES (58, 'Dulin', 'Grégory');
INSERT INTO T_BENEFICIARIES (BEN_ID, BEN_LASTNAME, BEN_FIRSTNAME) VALUES (59, 'Dermien', 'Alice');
INSERT INTO T_BENEFICIARIES (BEN_ID, BEN_LASTNAME, BEN_FIRSTNAME) VALUES (60, 'Prez', 'Anne-Sophie');;
INSERT INTO T_DAYS (DAY_ID, DAY_LBL) VALUES (1, 'Monday');
INSERT INTO T_DAYS (DAY_ID, DAY_LBL) VALUES (2, 'Tuesday');
INSERT INTO T_DAYS (DAY_ID, DAY_LBL) VALUES (3, 'Wednesday');
INSERT INTO T_DAYS (DAY_ID, DAY_LBL) VALUES (4, 'Thursday');
INSERT INTO T_DAYS (DAY_ID, DAY_LBL) VALUES (5, 'Friday');
INSERT INTO T_DAYS (DAY_ID, DAY_LBL) VALUES (6, 'Saturday');
INSERT INTO T_DAYS (DAY_ID, DAY_LBL) VALUES (7, 'Sunday');
INSERT INTO T_PLANNINGS (PLA_ID) VALUES (1);
INSERT INTO T_PLANNINGS (PLA_ID) VALUES (2);
INSERT INTO T_PLANNINGS (PLA_ID) VALUES (3);
INSERT INTO T_PLANNINGS (PLA_ID) VALUES (4);
INSERT INTO T_PLANNINGS (PLA_ID) VALUES (5);
INSERT INTO T_PLANNINGS (PLA_ID) VALUES (6);
INSERT INTO T_SLOTS (SLO_ID, PLA_ID, BEN_ID, DAY_ID, SLO_BEG, SLO_END) VALUES (1, 1, 26, 1, '08:00:00', '11:00:00');
INSERT INTO T_SLOTS (SLO_ID, PLA_ID, BEN_ID, DAY_ID, SLO_BEG, SLO_END) VALUES (2, 1, 1, 3, '13:30:00', '16:30:00');
INSERT INTO T_SLOTS (SLO_ID, PLA_ID, BEN_ID, DAY_ID, SLO_BEG, SLO_END) VALUES (3, 1, 16, 1, '14:00:00', '17:00:00');
INSERT INTO T_SLOTS (SLO_ID, PLA_ID, BEN_ID, DAY_ID, SLO_BEG, SLO_END) VALUES (4, 1, 25, 2, '08:00:00', '10:00:00');
INSERT INTO T_SLOTS (SLO_ID, PLA_ID, BEN_ID, DAY_ID, SLO_BEG, SLO_END) VALUES (5, 1, 25, 5, '14:00:00', '16:30:00');
INSERT INTO T_SLOTS (SLO_ID, PLA_ID, BEN_ID, DAY_ID, SLO_BEG, SLO_END) VALUES (6, 1, 7, 1, '11:00:00', '13:00:00');
INSERT INTO T_SLOTS (SLO_ID, PLA_ID, BEN_ID, DAY_ID, SLO_BEG, SLO_END) VALUES (7, 1, 20, 4, '08:00:00', '12:00:00');
INSERT INTO T_SLOTS (SLO_ID, PLA_ID, BEN_ID, DAY_ID, SLO_BEG, SLO_END) VALUES (8, 1, 29, 4, '13:30:00', '17:00:00');
INSERT INTO T_SLOTS (SLO_ID, PLA_ID, BEN_ID, DAY_ID, SLO_BEG, SLO_END) VALUES (9, 1, 9, 2, '10:00:00', '12:00:00');
INSERT INTO T_SLOTS (SLO_ID, PLA_ID, BEN_ID, DAY_ID, SLO_BEG, SLO_END) VALUES (10, 1, 4, 5, '08:00:00', '12:00:00');
INSERT INTO T_SLOTS (SLO_ID, PLA_ID, BEN_ID, DAY_ID, SLO_BEG, SLO_END) VALUES (11, 1, 30, 3, '16:30:00', '18:30:00');
INSERT INTO T_SLOTS (SLO_ID, PLA_ID, BEN_ID, DAY_ID, SLO_BEG, SLO_END) VALUES (12, 2, 15, 1, '08:00:00', '10:00:00');
INSERT INTO T_SLOTS (SLO_ID, PLA_ID, BEN_ID, DAY_ID, SLO_BEG, SLO_END) VALUES (13, 2, 15, 5, '16:00:00', '18:30:00');
INSERT INTO T_SLOTS (SLO_ID, PLA_ID, BEN_ID, DAY_ID, SLO_BEG, SLO_END) VALUES (14, 2, 11, 1, '14:00:00', '17:00:00');
INSERT INTO T_SLOTS (SLO_ID, PLA_ID, BEN_ID, DAY_ID, SLO_BEG, SLO_END) VALUES (15, 2, 24, 1, '10:00:00', '12:00:00');
INSERT INTO T_SLOTS (SLO_ID, PLA_ID, BEN_ID, DAY_ID, SLO_BEG, SLO_END) VALUES (16, 2, 24, 5, '08:00:00', '10:00:00');
INSERT INTO T_SLOTS (SLO_ID, PLA_ID, BEN_ID, DAY_ID, SLO_BEG, SLO_END) VALUES (17, 2, 17, 2, '08:00:00', '11:00:00');
INSERT INTO T_SLOTS (SLO_ID, PLA_ID, BEN_ID, DAY_ID, SLO_BEG, SLO_END) VALUES (18, 2, 27, 2, '11:00:00', '13:00:00');
INSERT INTO T_SLOTS (SLO_ID, PLA_ID, BEN_ID, DAY_ID, SLO_BEG, SLO_END) VALUES (19, 2, 27, 5, '10:00:00', '12:30:00');
INSERT INTO T_SLOTS (SLO_ID, PLA_ID, BEN_ID, DAY_ID, SLO_BEG, SLO_END) VALUES (20, 2, 22, 2, '14:00:00', '16:30:00');
INSERT INTO T_SLOTS (SLO_ID, PLA_ID, BEN_ID, DAY_ID, SLO_BEG, SLO_END) VALUES (21, 2, 22, 4, '16:00:00', '18:00:00');
INSERT INTO T_SLOTS (SLO_ID, PLA_ID, BEN_ID, DAY_ID, SLO_BEG, SLO_END) VALUES (22, 2, 8, 4, '13:30:00', '16:00:00');
INSERT INTO T_SLOTS (SLO_ID, PLA_ID, BEN_ID, DAY_ID, SLO_BEG, SLO_END) VALUES (23, 2, 3, 4, '08:00:00', '12:30:00');
INSERT INTO T_SLOTS (SLO_ID, PLA_ID, BEN_ID, DAY_ID, SLO_BEG, SLO_END) VALUES (24, 2, 13, 3, '14:30:00', '16:30:00');
INSERT INTO T_SLOTS (SLO_ID, PLA_ID, BEN_ID, DAY_ID, SLO_BEG, SLO_END) VALUES (25, 2, 13, 1, '17:00:00', '19:00:00');
INSERT INTO T_SLOTS (SLO_ID, PLA_ID, BEN_ID, DAY_ID, SLO_BEG, SLO_END) VALUES (26, 2, 2, 3, '08:00:00', '11:30:00');
INSERT INTO T_SLOTS (SLO_ID, PLA_ID, BEN_ID, DAY_ID, SLO_BEG, SLO_END) VALUES (27, 3, 19, 1, '08:30:00', '10:30:00');
INSERT INTO T_SLOTS (SLO_ID, PLA_ID, BEN_ID, DAY_ID, SLO_BEG, SLO_END) VALUES (28, 3, 19, 5, '08:00:00', '10:00:00');
INSERT INTO T_SLOTS (SLO_ID, PLA_ID, BEN_ID, DAY_ID, SLO_BEG, SLO_END) VALUES (29, 3, 12, 2, '08:00:00', '12:00:00');
INSERT INTO T_SLOTS (SLO_ID, PLA_ID, BEN_ID, DAY_ID, SLO_BEG, SLO_END) VALUES (30, 3, 6, 2, '13:30:00', '15:30:00');
INSERT INTO T_SLOTS (SLO_ID, PLA_ID, BEN_ID, DAY_ID, SLO_BEG, SLO_END) VALUES (31, 3, 6, 5, '13:30:00', '15:30:00');
INSERT INTO T_SLOTS (SLO_ID, PLA_ID, BEN_ID, DAY_ID, SLO_BEG, SLO_END) VALUES (32, 3, 10, 1, '10:30:00', '13:00:00');
INSERT INTO T_SLOTS (SLO_ID, PLA_ID, BEN_ID, DAY_ID, SLO_BEG, SLO_END) VALUES (33, 3, 28, 2, '15:30:00', '17:30:00');
INSERT INTO T_SLOTS (SLO_ID, PLA_ID, BEN_ID, DAY_ID, SLO_BEG, SLO_END) VALUES (34, 3, 28, 5, '15:30:00', '17:30:00');
INSERT INTO T_SLOTS (SLO_ID, PLA_ID, BEN_ID, DAY_ID, SLO_BEG, SLO_END) VALUES (35, 3, 14, 1, '14:00:00', '16:00:00');
INSERT INTO T_SLOTS (SLO_ID, PLA_ID, BEN_ID, DAY_ID, SLO_BEG, SLO_END) VALUES (36, 3, 5, 1, '16:00:00', '18:30:00');
INSERT INTO T_SLOTS (SLO_ID, PLA_ID, BEN_ID, DAY_ID, SLO_BEG, SLO_END) VALUES (37, 3, 5, 4, '14:00:00', '16:00:00');
INSERT INTO T_SLOTS (SLO_ID, PLA_ID, BEN_ID, DAY_ID, SLO_BEG, SLO_END) VALUES (38, 3, 21, 3, '08:00:00', '11:00:00');
INSERT INTO T_SLOTS (SLO_ID, PLA_ID, BEN_ID, DAY_ID, SLO_BEG, SLO_END) VALUES (39, 3, 21, 4, '16:00:00', '17:30:00');
INSERT INTO T_SLOTS (SLO_ID, PLA_ID, BEN_ID, DAY_ID, SLO_BEG, SLO_END) VALUES (40, 3, 18, 3, '13:00:00', '17:30:00');
INSERT INTO T_SLOTS (SLO_ID, PLA_ID, BEN_ID, DAY_ID, SLO_BEG, SLO_END) VALUES (41, 3, 23, 5, '10:00:00', '12:00:00');
INSERT INTO T_SLOTS (SLO_ID, PLA_ID, BEN_ID, DAY_ID, SLO_BEG, SLO_END) VALUES (42, 3, 23, 4, '08:00:00', '10:00:00');
INSERT INTO T_SLOTS (SLO_ID, PLA_ID, BEN_ID, DAY_ID, SLO_BEG, SLO_END) VALUES (43, 4, 31, 1, '08:00:00', '10:00:00');
INSERT INTO T_SLOTS (SLO_ID, PLA_ID, BEN_ID, DAY_ID, SLO_BEG, SLO_END) VALUES (44, 4, 31, 5, '08:00:00', '10:00:00');
INSERT INTO T_SLOTS (SLO_ID, PLA_ID, BEN_ID, DAY_ID, SLO_BEG, SLO_END) VALUES (45, 4, 32, 2, '08:00:00', '10:30:00');
INSERT INTO T_SLOTS (SLO_ID, PLA_ID, BEN_ID, DAY_ID, SLO_BEG, SLO_END) VALUES (46, 5, 44, 5, '15:30:00', '17:30:00');
INSERT INTO T_SLOTS (SLO_ID, PLA_ID, BEN_ID, DAY_ID, SLO_BEG, SLO_END) VALUES (47, 4, 32, 4, '08:00:00', '10:30:00');
INSERT INTO T_SLOTS (SLO_ID, PLA_ID, BEN_ID, DAY_ID, SLO_BEG, SLO_END) VALUES (48, 4, 33, 2, '14:00:00', '17:30:00');
INSERT INTO T_SLOTS (SLO_ID, PLA_ID, BEN_ID, DAY_ID, SLO_BEG, SLO_END) VALUES (49, 4, 40, 1, '10:00:00', '12:00:00');
INSERT INTO T_SLOTS (SLO_ID, PLA_ID, BEN_ID, DAY_ID, SLO_BEG, SLO_END) VALUES (50, 4, 40, 5, '10:00:00', '12:30:00');
INSERT INTO T_SLOTS (SLO_ID, PLA_ID, BEN_ID, DAY_ID, SLO_BEG, SLO_END) VALUES (51, 4, 38, 1, '14:00:00', '17:00:00');
INSERT INTO T_SLOTS (SLO_ID, PLA_ID, BEN_ID, DAY_ID, SLO_BEG, SLO_END) VALUES (52, 4, 38, 6, '08:00:00', '11:00:00');
INSERT INTO T_SLOTS (SLO_ID, PLA_ID, BEN_ID, DAY_ID, SLO_BEG, SLO_END) VALUES (53, 4, 35, 3, '08:00:00', '10:00:00');
INSERT INTO T_SLOTS (SLO_ID, PLA_ID, BEN_ID, DAY_ID, SLO_BEG, SLO_END) VALUES (54, 4, 36, 4, '13:30:00', '16:30:00');
INSERT INTO T_SLOTS (SLO_ID, PLA_ID, BEN_ID, DAY_ID, SLO_BEG, SLO_END) VALUES (55, 4, 34, 2, '10:30:00', '12:30:00');
INSERT INTO T_SLOTS (SLO_ID, PLA_ID, BEN_ID, DAY_ID, SLO_BEG, SLO_END) VALUES (56, 4, 34, 5, '14:00:00', '17:00:00');
INSERT INTO T_SLOTS (SLO_ID, PLA_ID, BEN_ID, DAY_ID, SLO_BEG, SLO_END) VALUES (57, 4, 39, 3, '10:00:00', '12:00:00');
INSERT INTO T_SLOTS (SLO_ID, PLA_ID, BEN_ID, DAY_ID, SLO_BEG, SLO_END) VALUES (58, 4, 37, 4, '10:30:00', '12:30:00');
INSERT INTO T_SLOTS (SLO_ID, PLA_ID, BEN_ID, DAY_ID, SLO_BEG, SLO_END) VALUES (59, 4, 37, 6, '11:00:00', '13:00:00');
INSERT INTO T_SLOTS (SLO_ID, PLA_ID, BEN_ID, DAY_ID, SLO_BEG, SLO_END) VALUES (60, 5, 46, 1, '08:00:00', '10:00:00');
INSERT INTO T_SLOTS (SLO_ID, PLA_ID, BEN_ID, DAY_ID, SLO_BEG, SLO_END) VALUES (61, 5, 42, 3, '13:30:00', '17:00:00');
INSERT INTO T_SLOTS (SLO_ID, PLA_ID, BEN_ID, DAY_ID, SLO_BEG, SLO_END) VALUES (62, 5, 41, 1, '14:00:00', '17:00:00');
INSERT INTO T_SLOTS (SLO_ID, PLA_ID, BEN_ID, DAY_ID, SLO_BEG, SLO_END) VALUES (63, 5, 48, 2, '08:00:00', '12:00:00');
INSERT INTO T_SLOTS (SLO_ID, PLA_ID, BEN_ID, DAY_ID, SLO_BEG, SLO_END) VALUES (64, 5, 47, 2, '13:30:00', '16:30:00');
INSERT INTO T_SLOTS (SLO_ID, PLA_ID, BEN_ID, DAY_ID, SLO_BEG, SLO_END) VALUES (65, 5, 45, 3, '08:00:00', '11:00:00');
INSERT INTO T_SLOTS (SLO_ID, PLA_ID, BEN_ID, DAY_ID, SLO_BEG, SLO_END) VALUES (66, 5, 46, 5, '08:00:00', '11:00:00');
INSERT INTO T_SLOTS (SLO_ID, PLA_ID, BEN_ID, DAY_ID, SLO_BEG, SLO_END) VALUES (67, 5, 43, 4, '08:00:00', '12:00:00');
INSERT INTO T_SLOTS (SLO_ID, PLA_ID, BEN_ID, DAY_ID, SLO_BEG, SLO_END) VALUES (68, 5, 49, 1, '10:00:00', '12:00:00');
INSERT INTO T_SLOTS (SLO_ID, PLA_ID, BEN_ID, DAY_ID, SLO_BEG, SLO_END) VALUES (69, 5, 49, 5, '13:00:00', '15:30:00');
INSERT INTO T_SLOTS (SLO_ID, PLA_ID, BEN_ID, DAY_ID, SLO_BEG, SLO_END) VALUES (70, 5, 50, 4, '13:00:00', '16:00:00');
INSERT INTO T_SLOTS (SLO_ID, PLA_ID, BEN_ID, DAY_ID, SLO_BEG, SLO_END) VALUES (71, 5, 44, 2, '16:30:00', '18:30:00');
INSERT INTO T_SLOTS (SLO_ID, PLA_ID, BEN_ID, DAY_ID, SLO_BEG, SLO_END) VALUES (72, 6, 60, 5, '16:00:00', '18:30:00');
INSERT INTO T_SLOTS (SLO_ID, PLA_ID, BEN_ID, DAY_ID, SLO_BEG, SLO_END) VALUES (73, 6, 57, 1, '08:00:00', '10:00:00');
INSERT INTO T_SLOTS (SLO_ID, PLA_ID, BEN_ID, DAY_ID, SLO_BEG, SLO_END) VALUES (74, 6, 57, 5, '14:00:00', '16:00:00');
INSERT INTO T_SLOTS (SLO_ID, PLA_ID, BEN_ID, DAY_ID, SLO_BEG, SLO_END) VALUES (75, 6, 55, 1, '14:00:00', '16:00:00');
INSERT INTO T_SLOTS (SLO_ID, PLA_ID, BEN_ID, DAY_ID, SLO_BEG, SLO_END) VALUES (76, 6, 55, 4, '14:00:00', '16:00:00');
INSERT INTO T_SLOTS (SLO_ID, PLA_ID, BEN_ID, DAY_ID, SLO_BEG, SLO_END) VALUES (77, 6, 51, 2, '08:00:00', '12:00:00');
INSERT INTO T_SLOTS (SLO_ID, PLA_ID, BEN_ID, DAY_ID, SLO_BEG, SLO_END) VALUES (78, 6, 59, 3, '08:00:00', '12:00:00');
INSERT INTO T_SLOTS (SLO_ID, PLA_ID, BEN_ID, DAY_ID, SLO_BEG, SLO_END) VALUES (79, 6, 58, 3, '13:30:00', '16:30:00');
INSERT INTO T_SLOTS (SLO_ID, PLA_ID, BEN_ID, DAY_ID, SLO_BEG, SLO_END) VALUES (80, 6, 54, 1, '16:00:00', '18:00:00');
INSERT INTO T_SLOTS (SLO_ID, PLA_ID, BEN_ID, DAY_ID, SLO_BEG, SLO_END) VALUES (81, 6, 54, 5, '08:00:00', '10:00:00');
INSERT INTO T_SLOTS (SLO_ID, PLA_ID, BEN_ID, DAY_ID, SLO_BEG, SLO_END) VALUES (82, 6, 56, 2, '13:00:00', '16:00:00');
INSERT INTO T_SLOTS (SLO_ID, PLA_ID, BEN_ID, DAY_ID, SLO_BEG, SLO_END) VALUES (83, 6, 52, 2, '16:00:00', '18:00:00');
INSERT INTO T_SLOTS (SLO_ID, PLA_ID, BEN_ID, DAY_ID, SLO_BEG, SLO_END) VALUES (84, 6, 52, 5, '10:00:00', '13:00:00');
INSERT INTO T_SLOTS (SLO_ID, PLA_ID, BEN_ID, DAY_ID, SLO_BEG, SLO_END) VALUES (85, 6, 53, 4, '08:30:00', '12:00:00');
INSERT INTO T_SLOTS (SLO_ID, PLA_ID, BEN_ID, DAY_ID, SLO_BEG, SLO_END) VALUES (86, 6, 60, 1, '10:00:00', '12:00:00');

I created this query:

SELECT
    PLA_ID,
    (EMP_LASTNAME || ' ' || EMP_FIRSTNAME) AS EMPLOYEE,
    CASE 
        WHEN DAY_ID = 1 
            THEN ('[' || SLO_BEG || ' -' || SLO_END || '] ' || BEN_LASTNAME || ' ' || BEN_FIRSTNAME) 
    END AS MONDAY,
    CASE 
        WHEN DAY_ID = 2 
            THEN ('[' || SLO_BEG || ' -' || SLO_END || '] ' || BEN_LASTNAME || ' ' || BEN_FIRSTNAME) 
    END AS TUESDAY,
    CASE 
        WHEN DAY_ID = 3 
            THEN ('[' || SLO_BEG || ' -' || SLO_END || '] ' || BEN_LASTNAME || ' ' || BEN_FIRSTNAME) 
    END AS WEDNESDAY,
    CASE 
        WHEN DAY_ID = 4 
            THEN ('[' || SLO_BEG || ' -' || SLO_END || '] ' || BEN_LASTNAME || ' ' || BEN_FIRSTNAME) 
    END AS THURSDAY,
    CASE 
        WHEN DAY_ID = 5 
            THEN ('[' || SLO_BEG || ' -' || SLO_END || '] ' || BEN_LASTNAME || ' ' || BEN_FIRSTNAME) 
    END AS FRIDAY,
    CASE 
        WHEN DAY_ID = 6 
            THEN ('[' || SLO_BEG || ' -' || SLO_END || '] ' || BEN_LASTNAME || ' ' || BEN_FIRSTNAME) 
    END AS SATURDAY,
    CASE 
        WHEN DAY_ID = 7 
            THEN ('[' || SLO_BEG || ' -' || SLO_END || '] ' || BEN_LASTNAME || ' ' || BEN_FIRSTNAME) 
    END AS SUNDAY
FROM
    T_PLANNINGS P
INNER JOIN 
    T_EMPLOYEES E ON P.PLA_ID = E.EMP_ID
INNER JOIN 
    T_SLOTS S ON P.PLA_ID = S.PLA_ID
INNER JOIN 
    T_BENEFICIARIES B ON S.BEN_ID = B.BEN_ID
INNER JOIN 
    T_DAYS D ON S.DAY_ID = D.DAY_ID

db<>fiddle

I get what is shown here:

employee monday tuesday wednesday thursday friday
Dermien Isabelle [08:00:00 -11:00:00] Bourget Jasmine
Dermien Isabelle [11:00:00 -13:00:00] Frouin Antoine
Dermien Isabelle [14:00:00 -17:00:00] Noblecourt Alain
Dermien Isabelle [08:00:00 -10:00:00] Charrier Pierre
Dermien Isabelle [10:00:00 -12:00:00] Masson Danika
Dermien Isabelle [13:30:00 -16:30:00] Duval Sophie
Dermien Isabelle [16:30:00 -18:30:00] Piron Victor
Dermien Isabelle [08:00:00 -12:00:00] Lecomte Sylvie
Dermien Isabelle [13:30:00 -17:00:00] Lefèvre Hélène
Dermien Isabelle [08:00:00 -12:00:00] Merlet Perrine
Dermien Isabelle [14:00:00 -16:30:00] Charrier Pierre

I'd like to obtain the compact "grouping" of rows as shown here:

employee monday tuesday wednesday thursday friday
Dermien Isabelle [08:00:00 -11:00:00] Bourget Jasmine [08:00:00 -10:00:00] Charrier Pierre [13:30:00 -16:30:00] Duval Sophie [08:00:00 -12:00:00] Lecomte Sylvie [08:00:00 -12:00:00] Merlet Perrine
Dermien Isabelle [11:00:00 -13:00:00] Frouin Antoine [10:00:00 -12:00:00] Masson Danika [16:30:00 -18:30:00] Piron Victor [13:30:00 -17:00:00] Lefèvre Hélène [14:00:00 -16:30:00] Charrier Pierre
Dermien Isabelle [14:00:00 -17:00:00] Noblecourt Alain

Solution

  • First of all, it would be easier to factorize the "formatting" part by getting '[' || SLO_BEG || ' -' || SLO_END || '] ' || BEN_LASTNAME || ' ' || BEN_FIRSTNAME outside of the day-by-day CASE, in a first step where DAY_ID is still a column instead of 7 columns.

    Then what we can do is flag each slot with its position in this day for this employee, thanks to the row_number() window function that helps comparing a row with its siblings (other rows with the same employee and day), within the resultset (that is, after the JOINs and WHERE have applied).
    Thus we can get this position thanks to:
    ROW_NUMBER() over (PARTITION BY P.PLA_ID, D.DAY_ID ORDER BY SLO_BEG)

    You then just have to output 1 row per employee and position (so group 3, 1 will be all first slots for employee 3: be it the monday first slot, tuesday first slot, and so on);
    on each of the slots, to get you can either PIVOT (which is uneasy in PostgreSQL, relying on the crosstab function),
    or manually pivot, using:

    But as we now are in a GROUP BY, we have to choose an aggregate function to only get 1 row's BEN_SLOT fill each column.
    As your CASE WHEN DAY_ID = 1 THEN BEN_SLOT END will only return a non-null value for 1 out of the 7 rows, and as most aggregate functions ignore NULL rows, we can use any of those aggregate function, so let's choose MAX, around the CASE:

        MAX(CASE WHEN DAY_ID = 1 THEN BEN_SLOT END) AS MONDAY, -- ← Return only 1 out of 7 values for this column; as 6 of them are NULL thanks to the CASE, this ensures we get only the intended value: MAX(NULL, NULL, X, NULL) = X).
        MAX(CASE WHEN DAY_ID = 2 THEN BEN_SLOT END) AS TUESDAY,
        …
        GROUP BY PLA_ID, position
    

    Thus your final query will be:

    WITH
      flat AS
      (
        SELECT
        P.PLA_ID,
        D.DAY_ID,
        ROW_NUMBER() over (PARTITION BY P.PLA_ID, D.DAY_ID ORDER BY SLO_BEG) position,
        (EMP_LASTNAME || ' ' || EMP_FIRSTNAME) AS EMPLOYEE,
        '[' || SLO_BEG || ' -' || SLO_END || '] ' || BEN_LASTNAME || ' ' || BEN_FIRSTNAME AS BEN_SLOT
        FROM T_PLANNINGS P
        INNER JOIN T_EMPLOYEES E ON P.PLA_ID = E.EMP_ID
        INNER JOIN T_SLOTS S ON P.PLA_ID = S.PLA_ID
        INNER JOIN T_BENEFICIARIES B ON S.BEN_ID = B.BEN_ID
        INNER JOIN T_DAYS D ON S.DAY_ID = D.DAY_ID
      )
    SELECT
      PLA_ID, EMPLOYEE,
        MAX(CASE WHEN DAY_ID = 1 THEN BEN_SLOT END) AS MONDAY,
        MAX(CASE WHEN DAY_ID = 2 THEN BEN_SLOT END) AS TUESDAY,
        MAX(CASE WHEN DAY_ID = 3 THEN BEN_SLOT END) AS WEDNESDAY,
        MAX(CASE WHEN DAY_ID = 4 THEN BEN_SLOT END) AS THURSDAY,
        MAX(CASE WHEN DAY_ID = 5 THEN BEN_SLOT END) AS FRIDAY,
        MAX(CASE WHEN DAY_ID = 6 THEN BEN_SLOT END) AS SATURDAY,
        MAX(CASE WHEN DAY_ID = 7 THEN BEN_SLOT END) AS SUNDAY
    FROM flat
    GROUP BY PLA_ID, EMPLOYEE, position
    ORDER BY PLA_ID, EMPLOYEE, position;
    
    pla_id employee monday tuesday wednesday thursday friday saturday sunday
    1 Dermien Isabelle [08:00:00 -11:00:00] Bourget Jasmine [08:00:00 -10:00:00] Charrier Pierre [13:30:00 -16:30:00] Duval Sophie [08:00:00 -12:00:00] Lecomte Sylvie [08:00:00 -12:00:00] Merlet Perrine null null
    1 Dermien Isabelle [11:00:00 -13:00:00] Frouin Antoine [10:00:00 -12:00:00] Masson Danika [16:30:00 -18:30:00] Piron Victor [13:30:00 -17:00:00] Lefèvre Hélène [14:00:00 -16:30:00] Charrier Pierre null null
    1 Dermien Isabelle [14:00:00 -17:00:00] Noblecourt Alain null null null null null null

    (see it as the first SELECT of a fiddle)

    Adapting to HSQLDB

    SELECT
      PLA_ID, EMPLOYEE,
        MAX(CASE WHEN DAY_ID = 1 THEN BEN_SLOT END) AS MONDAY,
        MAX(CASE WHEN DAY_ID = 2 THEN BEN_SLOT END) AS TUESDAY,
        MAX(CASE WHEN DAY_ID = 3 THEN BEN_SLOT END) AS WEDNESDAY,
        MAX(CASE WHEN DAY_ID = 4 THEN BEN_SLOT END) AS THURSDAY,
        MAX(CASE WHEN DAY_ID = 5 THEN BEN_SLOT END) AS FRIDAY,
        MAX(CASE WHEN DAY_ID = 6 THEN BEN_SLOT END) AS SATURDAY,
        MAX(CASE WHEN DAY_ID = 7 THEN BEN_SLOT END) AS SUNDAY
    FROM
    (
        SELECT
        P.PLA_ID,
        D.DAY_ID,
        (SELECT COUNT(1) FROM T_SLOTS SALL WHERE SALL.PLA_ID = S.PLA_ID AND SALL.DAY_ID = S.DAY_ID AND SALL.SLO_BEG < S.SLO_BEG) pos, -- Correlated subquery: we count strictly inferior SLO_BEGs, so our count will start from 0 instead of the 1 returned by row_number().
        (EMP_LASTNAME || ' ' || EMP_FIRSTNAME) AS EMPLOYEE,
        '[' || SLO_BEG || ' -' || SLO_END || '] ' || BEN_LASTNAME || ' ' || BEN_FIRSTNAME AS BEN_SLOT
        FROM T_PLANNINGS P
        INNER JOIN T_EMPLOYEES E ON P.PLA_ID = E.EMP_ID
        INNER JOIN T_SLOTS S ON P.PLA_ID = S.PLA_ID
        INNER JOIN T_BENEFICIARIES B ON S.BEN_ID = B.BEN_ID
        INNER JOIN T_DAYS D ON S.DAY_ID = D.DAY_ID
    ) flat
    GROUP BY PLA_ID, EMPLOYEE, pos
    ORDER BY PLA_ID, EMPLOYEE, pos;
    

    (see it running as the last SELECT from the fiddle)