Concatenating Multiple Row Values into a Single Comma-Separated List
In scenarios where you need to consolidate multiple rows into a single, comma-separated value, you can achieve this using FOR XML PATH. This script demonstrates how to retrieve volunteer data and display the days they have selected for participation.
The approach uses a cross-reference table to link volunteers with the days they have chosen. The result is displayed in a single row per volunteer, with their selected days concatenated into a string.
You can also use STRING_AGG (SQL Server 2017+) to achieve same results. I will submit separate example about it. This script is focused on FOR XML PATH method.
Disclaimer: Before using this script, ensure that it is compatible with your version of SQL Server, as some features or syntax may vary between versions. Always explore if there are more efficient or alternative methods available for your specific use case. This script is provided as an example, and you should use it at your own discretion. Test thoroughly in a non-production environment to confirm its effectiveness and ensure it meets your requirements.
-- 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 
