Replicating GROUP_CONCAT Function of MySQL in SQL Server RRS feed

  • Question

  • I have a table named ss_1 that has columns as roll_no, name and marks. I need to display the marks and the student names who have obatined the same marks in single row. This can be easily be done in MySQL that has the functionality of GROUP_CONCAT which concats any column by the group by clause.

    The structure and entries in my table ss_1 is:

    roll_no name marks
    1 Rohan 70
    2 Rahul 70
    5 Saheb 70
    8 Arun 75
    8 Benn 75

    To group names according to marks in single row, we have to use the following query:

    distinct Marks
    ,NameList=STUFF((SELECT ','+name FROM ss_1 WHERE marks=A.marks FOR XML PATH('')) , 1 , 1 , '' )
    ss_1 A
    order by 1 desc

    Here my table name is ss_1 and for conacatanating a column values we have to use the FOR XML PATH("") syntax.
    The STUFF function inserts a string into another string. It deletes a specified length of characters in the first string at the start position and then inserts the second string into the first string at the start position.

    The syntax of STUFF function is :STUFF (character_expression , start , length ,character_expression ) where

    Is an expression of character data. character_expression can be a constant, variable, or column of either character or binary data.

    Is an integer value that specifies the location to start deletion and insertion. If start or length is negative, a null string is returned. If start is longer than the first character_expression, a null string is returned. start can be of type bigint.

    Is an integer that specifies the number of characters to delete. If length is longer than the first character_expression, deletion occurs up to the last character in the last character_expression. length can be of type bigint.

    The output of the following SQL is

    Marks NameList
    75 Arun,Benn
    70 Rohan,Rahul,Saheb

    So, in this way GROUP_CONCAT can be implemented in SQL Server
    • Changed type Tom Phillips Tuesday, December 4, 2012 5:09 PM
    Tuesday, January 25, 2011 10:45 AM


All replies

  • The only way that I have been able to do this in the past is using a SQL CLR aggregation. I have rolled my own in the past but it looks like there is a condeplex project that may fit the bill for you:



    Wednesday, July 13, 2011 7:11 PM
  • I'm thinking we may want to have both SPLIT and CONCATENATE functions built-in the language. Wondering if this is already in Denali or going to be in a different version?
    For every expert, there is an equal and opposite expert. - Becker's Law

    My blog
    • Marked as answer by Kalman Toth Monday, December 10, 2012 2:46 PM
    • Unmarked as answer by Kalman Toth Monday, December 10, 2012 2:46 PM
    Wednesday, July 13, 2011 7:59 PM
  • This is genius.  Thanks so much for posting it.
    Tuesday, December 4, 2012 10:41 AM