One of the T-SQL functions introduced in SQL Server 2017 is the STRING_AGG() function. This is basically the equivalent of MySQL’s GROUP_CONCAT() function – it lets you return query results as a delimited list, rather than in rows.

But there are a few minor differences between the two functions.

This article explores some of the main syntax differences between these functions.

Syntax

First off, here’s the official syntax for each function.

MySQL – GROUP_CONCAT()

GROUP_CONCAT([DISTINCT] expr [,expr ...]
             [ORDER BY {unsigned_integer | col_name | expr}
                 [ASC | DESC] [,col_name ...]]
             [SEPARATOR str_val])

T-SQL – STRING_AGG()

STRING_AGG ( expression, separator ) [ <order_clause> ]

<order_clause> ::=   
    WITHIN GROUP ( ORDER BY <order_by_expression_list> [ ASC | DESC ] )

Syntax Differences

Here are the three main syntax differences between MySQL’s GROUP_CONCAT() and T-SQL’s STRING_AGG() functions:

• Default Separator: Probably the most obvious difference is the fact that STRING_AGG() requires you to specify a separator. If you don’t provide two arguments (the second of which is the separator) you’ll get an error. With MySQL’s GROUP_CONCAT() function on the other hand, the separator is an optional argument. If you don’t provide it, it will use a comma by default.
• Ordering the Results: While both MySQL’s and T-SQL’s functions allow you to add an ORDER BY clause, the syntax is slightly different. T-SQL requires you to use the WITHIN GROUP clause when ordering the result set, whereas MySQL doesn’t have this requirement.
• Distinct Results: MySQL allows you to use DISTINCT to return only unique values. T-SQL doesn’t provide this option.

Below are examples to demonstrate these differences.

Default Separator

MySQL – GROUP_CONCAT()

We don’t need to specify the separator in MySQL. This is an optional argument. The default value is a comma.

SELECT GROUP_CONCAT(Genre) AS Result
FROM Genres;

Result:

+----------------------------------------------+
| Result                                       |
+----------------------------------------------+
| Rock,Jazz,Country,Pop,Blues,Hip Hop,Rap,Punk |
+----------------------------------------------+

T-SQL – STRING_AGG()

T-SQL requires us to specify the separator.

SELECT STRING_AGG(Genre, ',') AS Result
FROM Genres;

Result:

Result                                      
--------------------------------------------
Rock,Jazz,Country,Pop,Blues,Hip Hop,Rap,Punk

If we don’t specify a separator we get an error:

SELECT STRING_AGG(Genre) AS Result
FROM Genres;

Result:

Error: The STRING_AGG function requires 2 argument(s).

Ordering the Results

MySQL – GROUP_CONCAT()

When ordering the result set in MySQL, simply add the ORDER BY clause as an argument, followed by the column to order it by, followed by either ASC or DESC depending on whether you want it in ascending or descending order.

USE Music;
SELECT 
  ar.ArtistName AS 'Artist',
  GROUP_CONCAT(al.AlbumName ORDER BY al.AlbumName DESC) AS 'Album List'
FROM Artists ar
INNER JOIN Albums al
ON ar.ArtistId = al.ArtistId
GROUP BY ArtistName;

Result:

+------------------------+----------------------------------------------------------------------------+
| Artist                 | Album List                                                                 |
+------------------------+----------------------------------------------------------------------------+
| AC/DC                  | Powerage                                                                   |
| Allan Holdsworth       | The Sixteen Men of Tain,All Night Wrong                                    |
| Buddy Rich             | Big Swing Face                                                             |
| Devin Townsend         | Ziltoid the Omniscient,Epicloud,Casualties of Cool                         |
| Iron Maiden            | Somewhere in Time,Powerslave,Piece of Mind,No Prayer for the Dying,Killers |
| Jim Reeves             | Singing Down the Lane                                                      |
| Michael Learns to Rock | Scandinavia,Eternity,Blue Night                                            |
| The Script             | No Sound Without Silence                                                   |
| Tom Jones              | Praise and Blame,Long Lost Suitcase,Along Came Jones                       |
+------------------------+----------------------------------------------------------------------------+

T-SQL – STRING_AGG()

When ordering the concatenated results with ORDER BY,  SQL Server requires that the WITHIN GROUP clause be used.

USE Music;
SELECT 
  ar.ArtistName AS 'Artist',
  STRING_AGG(al.AlbumName, ', ') WITHIN GROUP (ORDER BY al.AlbumName DESC) AS 'Album List'
FROM Artists ar
INNER JOIN Albums al
ON ar.ArtistId = al.ArtistId
GROUP BY ArtistName;

Result:

Artist                     Album List                                                                    
-------------------------  ------------------------------------------------------------------------------
AC/DC                      Powerage                                                                      
Allan Holdsworth           The Sixteen Men of Tain, All Night Wrong                                      
Buddy Rich                 Big Swing Face                                                                
Devin Townsend             Ziltoid the Omniscient, Epicloud, Casualties of Cool                          
Iron Maiden                Somewhere in Time, Powerslave, Piece of Mind, No Prayer for the Dying, Killers
Jim Reeves                 Singing Down the Lane                                                         
Michael Learns to Rock     Scandinavia, Eternity, Blue Night                                             
The Script                 No Sound Without Silence                                                      
Tom Jones                  Praise and Blame, Long Lost Suitcase, Along Came Jones                        

Distinct Results

MySQL – GROUP_CONCAT()

MySQL’s GROUP_CONCAT() supports the DISTINCT clause, which allows you to eliminate duplicate values from the result set.

USE Solutions;
SELECT GROUP_CONCAT(DISTINCT TaskName) 
FROM Tasks;

Result:

+--------------------------------------------------------+
| GROUP_CONCAT(DISTINCT TaskName)                        |
+--------------------------------------------------------+
| Do garden,Feed cats,Paint roof,Relax,Take dog for walk |
+--------------------------------------------------------+

T-SQL – STRING_AGG()

T-SQL’s STRING_AGG() function doesn’t support the DISTINCT clause.

USE Solutions;
SELECT STRING_AGG(DISTINCT TaskName, ',') 
FROM Tasks;

Result:

Error: Incorrect syntax near ','.

As expected, an error occurs if we try to use the DISTINCT clause with STRING_AGG().