none
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:


    SELECT
    distinct Marks
    ,NameList=STUFF((SELECT ','+name FROM ss_1 WHERE marks=A.marks FOR XML PATH('')) , 1 , 1 , '' )
    FROM
    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

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

    start
    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.

    length
    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
    Tuesday, January 25, 2011 10:45 AM

Answers

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:

    http://groupconcat.codeplex.com/

     

    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
    Wednesday, July 13, 2011 7:59 PM
    Moderator
  • This is genius.  Thanks so much for posting it.
    Tuesday, December 4, 2012 10:41 AM