SQL Exercise – 9

SQL Exercise:

You are given a table containing a list of departments and employees. Write a query to concatenate all employee names for each department into a single comma-separated string. Also, sort the employee names alphabetically in the concatenated list.

Create table script:

CREATE TABLE Employee (
EmployeeID INT PRIMARY KEY,
EmployeeName NVARCHAR(50),
Department NVARCHAR(50)
);

Insert sample data script:

INSERT INTO Employee (EmployeeID, EmployeeName, Department)
VALUES
(1, 'Amit', 'HR'),
(2, 'Priya', 'HR'),
(3, 'Rohit', 'Finance'),
(4, 'Meera', 'Finance'),
(5, 'Karan', 'Finance'),
(6, 'Ananya', 'IT'),
(7, 'Suresh', 'IT');

Solution:

SELECT 
Department,
STRING_AGG(EmployeeName, ', ') WITHIN GROUP (ORDER BY EmployeeName) AS EmployeeList
FROM 
Employee
GROUP BY 
Department;

Output:

Explanation:

• STRING_AGG(EmployeeName, ‘, ‘):
  • This function aggregates multiple EmployeeName values into a single string, separated by ‘, ‘.
• WITHIN GROUP (ORDER BY EmployeeName):
  • Ensures that the concatenated names are sorted alphabetically (ORDER BY EmployeeName) before being aggregated.