SQL SERVER – GROUP BY Columns with XMLPATH – Comma Delimit Multiple Rows

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.

SQL SERVER - GROUP BY Columns with XMLPATH - Comma Delimit Multiple Rows byxmlpath

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())[1]','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)

,
Previous Post
SQL SERVER – ​Building Technical Reference Library – Notes from the Field #048
Next Post
SQL SERVER – How to Find Running Total in SQL Server

Related Posts

4 Comments. Leave new

  • Well, if we know in advance maximum size of the classes, we may improve your query (particular solution of course :) ):
    select ClassID, STUFF (CONCAT ([1],[2],[3],[4],[5]), 1, 2, ”)
    from (select ClassID
    , ‘, ‘ + FirstName + ‘ ‘ + LastName as [name]
    , ROW_NUMBER () OVER (PARTITION BY ClassID ORDER BY (SELECT (0))) as [rn] — any order you want
    from StudentEnrolled
    )
    data
    pivot (MAX (name) for rn in ([1],[2],[3],[4],[5]) — let’s say maximum size of class is 5
    )
    pvt

    Reply
  • A bit of a noob here but couldn’t we use the coalesce function or even create a Join function to accomplish this?

    Reply
  • Dear Dave,
    I’m newbie here and I found your blog is really help me very much.
    I have question if the data like below, and how to groupng the BillToxxx in One column ?
    JobNum Office HQ BillTo1 BillTo2 BillTo3 BillTo4 BillTo5 BillTo6
    MSBSE0003840 MSB; CGO; 121001648 121001648-01 121001648-02 121001648-03 121001648-04 121002455
    MSBSE0003922 MSB; CGO; 121001648 121001648-01 121001648-02 121001648-03 121001648-04 121002455

    Reply

Leave a Reply

Menu