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 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64
| CREATE TABLE Participations
(
NomParticipant VARCHAR(20),
Bloc CHAR(2),
Presence CHAR(1)
)
GO
INSERT INTO Participations VALUES ('Ali', 'B1', 'V')
INSERT INTO Participations VALUES ('Ali', 'B2', 'V')
INSERT INTO Participations VALUES ('Ali', 'B3', 'F')
INSERT INTO Participations VALUES ('Nadine', 'B1', 'F')
INSERT INTO Participations VALUES ('Nadine', 'B5', 'V')
INSERT INTO Participations VALUES ('Sara', 'B1', 'V')
INSERT INTO Participations VALUES ('Sara', 'B6', 'V')
GO
SELECT NomParticipant,
CASE B1 WHEN 1 THEN 'F' ELSE 'V' END AS B1,
CASE B2 WHEN 1 THEN 'F' ELSE 'V' END AS B2,
CASE B3 WHEN 1 THEN 'F' ELSE 'V' END AS B3,
CASE B4 WHEN 1 THEN 'F' ELSE 'V' END AS B4,
CASE B5 WHEN 1 THEN 'F' ELSE 'V' END AS B5,
CASE B6 WHEN 1 THEN 'F' ELSE 'V' END AS B6
FROM
(
SELECT NomParticipant,
SUM(CASE B1 WHEN 'V' THEN 1 ELSE 0 END) AS B1,
SUM(CASE B2 WHEN 'V' THEN 1 ELSE 0 END) AS B2,
SUM(CASE B3 WHEN 'V' THEN 1 ELSE 0 END) AS B3,
SUM(CASE B4 WHEN 'V' THEN 1 ELSE 0 END) AS B4,
SUM(CASE B5 WHEN 'V' THEN 1 ELSE 0 END) AS B5,
SUM(CASE B6 WHEN 'V' THEN 1 ELSE 0 END) AS B6
FROM
(
SELECT NomParticipant,
CASE Bloc
WHEN 'B1' THEN Presence
ELSE 'F'
END AS B1,
CASE Bloc
WHEN 'B2' THEN Presence
ELSE 'F'
END AS B2,
CASE Bloc
WHEN 'B3' THEN Presence
ELSE 'F'
END AS B3,
CASE Bloc
WHEN 'B4' THEN Presence
ELSE 'F'
END AS B4,
CASE Bloc
WHEN 'B5' THEN Presence
ELSE 'F'
END AS B5,
CASE Bloc
WHEN 'B6' THEN Presence
ELSE 'F'
END AS B6
FROM Participations
) AS TMP
GROUP BY NomParticipant
) AS FINAL |
Partager