This is one of the most popular question and I keep on getting again and again in email, Facebook and on social media. I have decided to write about it here in the blog so in future I can directly give a reference.
Here is the question – there is the question. There is a table with name of the student and their classid, now we have to create another table where we have different representation of the classid and student names. In simple words, we have to group by classid and concat user names. Here is how image representations of the same.
Here is the script of the original table which generates a table displayed on the left side of the image.
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 -- SELECT * FROM StudentEnrolled GO
Now we can use XMLPATH to concat the firstname and lastname of the student and along with that we can also group by them using following script. Now this was just an example, but you can in future use this script for many other purposes.
SELECT [ClassID], STUFF(( SELECT ', ' + [FirstName] + ' ' + [LastName] FROM StudentEnrolled WHERE (ClassID = SE.ClassID) FOR XML PATH(''),TYPE).value('(./text())','VARCHAR(MAX)'),1,2,'') AS FullName FROM StudentEnrolled SE GROUP BY ClassID GO
Let me know if there is any better way to do the same.
Reference: Pinal Dave (https://blog.sqlauthority.com)