Using STRING_AGG to Concatenate Employee Names

Use STRING_AGG to Concatenate Strings

Write a SQL query to concatenate employee names within each department into a single string.

Solution:

-- Concatenate employee names within each department.
SELECT 
    DepartmentID,
    STRING_AGG(Name, ', ') AS EmployeeNames
FROM Employees
GROUP BY DepartmentID;

Explanation:

1. Purpose of the Query :

1. The goal is to demonstrate how to use STRING_AGG to concatenate strings (e.g., employee names) within groups (e.g., departments).

2. Key Components :

1. STRING_AGG(Name, ', ') : Concatenates employee names, separated by a comma.
2. GROUP BY DepartmentID : Groups employees by department.

3. Why use STRING_AGG? :

1. STRING_AGG simplifies string concatenation across rows, replacing older methods like FOR XML PATH.

4. Real-World Application :

1. For example, in reporting systems, you might use this query to generate a list of employees grouped by department.

Additional Notes:

• Ensure that the delimiter (e.g., , ) is appropriate for the context.
• Use this exercise to teach how to aggregate strings efficiently.

For more Practice: Solve these Related Problems:

• Write a SQL query to concatenate product names within each category into a single string separated by semicolons.
• Write a SQL query to generate a comma-separated list of all customers in each region.
• Write a SQL query to create a summary of skills possessed by employees in each department.
• Write a SQL query to concatenate all tags associated with blog posts into a single string.

Go to:

• Master Advanced SQL Exercises Home ↩
• SQL Exercises Home ↩

PREV : Use PERCENT_RANK and CUME_DIST Functions.
NEXT : Handle JSON Arrays with Nested Queries.

Have another way to solve this solution? Contribute your code (and comments) through Disqus.