-- Create Volunteers table
CREATE TABLE Volunteers (
VolunteerID INT PRIMARY KEY,
VolunteerName VARCHAR(100)
) -- Create DaysOfWeek table
CREATE TABLE DaysOfWeek (
DayID INT PRIMARY KEY,
NameofDay VARCHAR(20)
) -- Create VolunteerDays cross-reference table
CREATE TABLE VolunteerDays (
VolunteerID INT,
DayID INT,
PRIMARY KEY (VolunteerID, DayID),
FOREIGN KEY (VolunteerID) REFERENCES Volunteers(VolunteerID),
FOREIGN KEY (DayID) REFERENCES DaysOfWeek(DayID)
) -- Insert sample data into Volunteers table
INSERT INTO Volunteers (VolunteerID, VolunteerName)
VALUES
(1, 'John'),
(2, 'Joseph'),
(3, 'Mary') -- Insert sample data into DaysOfWeek table
INSERT INTO DaysOfWeek (DayID, NameofDay)
VALUES
(1, 'Monday'),
(2, 'Tuesday'),
(3, 'Wednesday'),
(4, 'Thursday'),
(5, 'Friday'),
(6, 'Saturday'),
(7, 'Sunday') -- Insert sample data into VolunteerDays cross-reference table
INSERT INTO VolunteerDays (VolunteerID, DayID)
VALUES
(1, 1), -- John -> Monday
(1, 3), -- John -> Wednesday
(2, 2), -- Joseph -> Tuesday
(2, 6), -- Joseph -> Saturday
(3, 5), -- Mary -> Friday
(3, 7) -- Mary -> Sunday -- Below qyery uses FOR XML PATH to achieve results
SELECT
VOL.VolunteerName,
STUFF(
(
SELECT ', ' + DOW.NameofDay
FROM
VolunteerDays VDY
INNER JOIN DaysOfWeek DOW ON
VDY.DayID = DOW.DayID
WHERE
VDY.VolunteerID = VOL.VolunteerID
FOR XML PATH(''), TYPE).value('.', 'NVARCHAR(MAX)'), 1, 2, '') AS SelectedDays
FROM
Volunteers VOL |
