RaqForum 41 No.
1,130 View  • 
     ...

* How to Concatenate Strings in a Group for SQL Server

 
mysql(8) sql(179) split(3) merge(3) sql server(1)

To achieve a MySQL group_concat-like operation, which is concatenating strings in a group, SQL Server 2017 uses STRING_AGG function, such as SELECT G, STRING_AGG(S, ',') FROM T GROUP BY G (Group table T by G field and concatenate values of S field in each group). But for users using SQL Server 2016, they can’t do this because the version doesn’t provide the function. So things turn a hassle as shown below:

SELECT p2.G,

SS = JSON_VALUE(

  REPLACE(

 (SELECT _  = p1.S FROM T p1 WHERE p1.G = p2.G FOR JSON   PATH)

    ,'"},{"_":"',',   '),'$[0]._'

)

FROM T p2

 

SQL Server 2014 even needs a 3-level nested query and the help of XML to do the concatenation. The code is complicated.

SELECT Main.G,

LEFT(Main.SS,Len(Main.SS)-1) As "SS"

FROM

(

SELECT  DISTINCT p2.G,

(

SELECT p1.S + ',' AS [text()]

FROM T p1

WHERE p1.G = p2.G

ORDER BY p1.G

FOR XML PATH ('')

)[SS]

FROM T  p2

[Main]

 

Other versions of SQL Server have their own ways to achieve the concatenation too. But all is not convenient.

If you happen to use a SQL Server version that doesn’t offer a group-concat-like function, you can use esProc to do the job. Code will be rather simple. For SQL Server2014, esProc concatenate strings in a group using the following script:

A

B

1

=db.query("select G, S   from T")

/query from SQL Server 2014

2

=A1.group(G;   ~.(S).concat(","))

/Concatenate values in   multiple rows with commas

Not only the method achieves the group_concat-like effect, but it applies any database.

 

esProc is a scripting language that allows repeated execution on IDE and being called from the command line. It provides convenient and simple alternative to complicated SQL methods. Here are more examples:

Overcome SQL Headache: Intuitive Grouping
Simplifying SQL with SPL: Interrow computation
Simplifying SQL with SPL:Getting TopN from Each Group
Simplifying SQL with SPL:Multilevel Grouping by Fixed Condition
Learn esProc algorithms HERE.

esProc boasts agile syntax and a rich variety class libraries for structured data processing that enables convenient, universal methods for scenarios that SQL struggles to handle awkwardly and cumbersomely. See SQL Enhancer.

Read Getting Started with esProc to download and install esProc, get a license for free and find related documentation.

mysql(8) sql(179) split(3) merge(3) sql server(1)
 
 •  1,130 View  •