Post reply

Replicating GROUP_CONCAT Function of MySQL in SQL Server

  • May 14, 2013 at 1:13 am

    #277527

    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_nonamemarks

    1Rohan70

    2Rahul70

    5Saheb70

    8Arun75

    8Benn75

    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 concatanating 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

    MarksNameList

    75Arun,Benn

    70Rohan,Rahul,Saheb

    So, in this way GROUP_CONCAT can be implemented in SQL Server

  • May 14, 2013 at 2:29 am

    #1614859

    Using FOR XML PATH is most common technique to achieve string/row concatenation.

    So, what is your question?

    _____________________________________________
    "The only true wisdom is in knowing you know nothing"
    "O skol'ko nam otkrytiy chudnyh prevnosit microsofta duh!":-D
    (So many miracle inventions provided by MS to us...)

    How to post your question to get the best and quick help[/url]

Viewing 2 posts - 1 through 1 (of 1 total)

You must be logged in to reply to this topic. Login to reply