Before SQL Server 2017 (14.x), you can achieve the comma-separated values using the XML method. Here's an example of how you can achieve this:
DECLARE @StateCity TABLE(
StateID INT,
StateName VARCHAR(100) NOT NULL,
CityID INT,
CityName VARCHAR(100) NOT NULL,
PRIMARY KEY (StateID, CityID) -- Composite primary key on StateID and CityID
);
INSERT INTO @StateCity (StateID, StateName, CityID, CityName)
VALUES
(1, 'Rajasthan', 101, 'Kota'),
(1, 'Rajasthan', 102, 'Bundi'),
(1, 'Rajasthan', 103, 'Jaipur'),
(1, 'Rajasthan', 104, 'Jhalawar'),
(1, 'Rajasthan', 105, 'Sikar'),
(1, 'Rajasthan', 106, 'Baran');
INSERT INTO @StateCity (StateID, StateName, CityID, CityName)
VALUES
(2, 'Maharashtra', 201, 'Mumbai'),
(2, 'Maharashtra', 202, 'Pune'),
(2, 'Maharashtra', 203, 'Nagpur'),
(2, 'Maharashtra', 204, 'Nashik'),
(2, 'Maharashtra', 205, 'Aurangabad');
INSERT INTO @StateCity (StateID, StateName, CityID, CityName)
VALUES
(3, 'Uttar Pradesh', 301, 'Lucknow'),
(3, 'Uttar Pradesh', 302, 'Kanpur'),
(3, 'Uttar Pradesh', 303, 'Agra'),
(3, 'Uttar Pradesh', 304, 'Varanasi'),
(3, 'Uttar Pradesh', 305, 'Allahabad');
SELECT * FROM @StateCity;
SELECT
B.StateName,
STUFF((SELECT ','+A.CityName FROM @StateCity A WHERE A.StateID = B.StateID
FOR XML PATH('')),1,1,'') as CityConcate
FROM @StateCity B
--STUFF(string, start, length, new_string)This query using the STUFF and FOR XML PATH method will produce the same result as the function introduced in SQL Server 2017 and later.
STRING_AGG is a function in SQL that concatenates values from a column into a single string with a specified separator. This function is useful for creating comma-separated lists or other delimited strings from the values in a column.
Here's an example of how to use the STRING_AGG function:
SELECT Department, STRING_AGG(EmployeeName, ', ') AS Employees
FROM EmployeeTable
GROUP BY Department;
In this example, assuming EmployeeTable has columns Department and EmployeeName, the STRING_AGG function concatenates the EmployeeName values for each department, separated by a comma and space. For instance, it could result in a table like this:
| Department | Employees |
|---|---|
| Sales | John Doe, Jane Smith |
| Marketing | Alex Johnson, Sarah Brown |
This effectively creates a list of employees for each department using the STRING_AGG function.