SQL Server itself does not contain the LISTAGG function, but similar functions can be achieved through various methods.
<code class="language-sql">SELECT FieldA, GROUP_CONCAT(FieldB ORDER BY FieldB SEPARATOR ',') AS FieldBs FROM TableName GROUP BY FieldA ORDER BY FieldA;</code>
<code class="language-sql">SELECT FieldA, LISTAGG(FieldB, ',') WITHIN GROUP (ORDER BY FieldB) AS FieldBs FROM TableName GROUP BY FieldA ORDER BY FieldA;</code>
<code class="language-sql">SELECT FieldA, STRING_AGG(FieldB, ',' ORDER BY FieldB) AS FieldBs FROM TableName GROUP BY FieldA ORDER BY FieldA;</code>
SQL Server >= 2017 & Azure SQL
<code class="language-sql">SELECT FieldA, STRING_AGG(FieldB, ',') WITHIN GROUP (ORDER BY FieldB) AS FieldBs FROM TableName GROUP BY FieldA ORDER BY FieldA;</code>
SQL Server (other versions)
For code readability and maintainability, common table expressions (CTE) are used here:
<code class="language-sql">WITH CTE_TableName AS ( SELECT FieldA, FieldB FROM TableName ) SELECT t0.FieldA, STUFF( ( SELECT ',' + t1.FieldB FROM CTE_TableName t1 WHERE t1.FieldA = t0.FieldA ORDER BY t1.FieldB FOR XML PATH('') ), 1, LEN(','), '' ) AS FieldBs FROM CTE_TableName t0 GROUP BY t0.FieldA ORDER BY FieldA;</code>
When sorting is required, you need to use CTE or subquery
<code class="language-sql">WITH CTE_TableName AS ( SELECT FieldA, FieldB FROM TableName ORDER BY FieldA, FieldB ) SELECT FieldA, GROUP_CONCAT(FieldB, ',') AS FieldBs FROM CTE_TableName GROUP BY FieldA ORDER BY FieldA;</code>
When no sorting is required
<code class="language-sql">SELECT FieldA, GROUP_CONCAT(FieldB, ',') AS FieldBs FROM TableName GROUP BY FieldA ORDER BY FieldA;</code>
The above is the detailed content of How to Replicate Oracle's LISTAGG Function in SQL Server?. For more information, please follow other related articles on the PHP Chinese website!