One of the common tasks in SQL Server is to create a new table that contains grouped and concatenated data from an existing table. This can be useful when you want to summarize data or create a new representation of the original data. In this article, we will explore how to achieve this using SQL Server.

Let’s consider a scenario where we have a table called “StudentEnrolled” with columns for ClassID, FirstName, and LastName. Our goal is to create a new table where we group the data by ClassID and concatenate the first and last names of the students in each class.

First, let’s create the original table:

USE tempdb
GO
CREATE TABLE StudentEnrolled (ClassID INT, FirstName VARCHAR(20), LastName VARCHAR(20))
GO
INSERT INTO StudentEnrolled (ClassID, FirstName, LastName)
SELECT 1, 'Thomas', 'Callan'
UNION ALL
SELECT 1, 'Henry', 'Quinto'
UNION ALL
SELECT 2, 'Greg', 'McCarthy'
UNION ALL
SELECT 2, 'Brad', 'Grey'
UNION ALL
SELECT 2, 'Loren', 'Oliver'
UNION ALL
SELECT 3, 'Elliot', 'Kirkland'
GO

Now, let’s use the XMLPATH function to concatenate the first and last names of the students and group them by ClassID:

SELECT
[ClassID],
STUFF((
SELECT ', ' + [FirstName] + ' ' + [LastName]
FROM StudentEnrolled
WHERE (ClassID = SE.ClassID)
FOR XML PATH(''),TYPE).value('(./text())[1]','VARCHAR(MAX)'),1,2,'') AS FullName
FROM StudentEnrolled SE
GROUP BY ClassID
GO

This query uses the XMLPATH function to concatenate the first and last names of the students in each class. The STUFF function is then used to remove the leading comma and space from the concatenated string. Finally, the result is grouped by ClassID.

By executing this query, we will get a new table with the desired representation of the data, where each row represents a class and the FullName column contains the concatenated names of the students in that class.

This technique can be used for various purposes, such as generating reports, creating summary tables, or exporting data in a specific format. It provides a flexible way to manipulate and transform data in SQL Server.

Feel free to explore other methods or share if you have a better approach to achieve the same result.