How to merge multiple rows in SQL Server into one row?

2 years ago
William Carter
1 minute

You can merge multiple lines into one by using the following method:

  1. To generate XML without using a specific tag name.
SELECT STUFF((SELECT ',' + ColumnName
              FROM TableName
              FOR XML PATH('')), 1, 1, '') AS MergedColumn
FROM TableName
  1. combine a string into one aggregated string
SELECT STRING_AGG(ColumnName, ',') AS MergedColumn
FROM TableName
  1. Utilize recursive CTE (common table expressions) technique:
WITH RecursiveCTE AS (
    SELECT ColumnName, ROW_NUMBER() OVER (ORDER BY ColumnName) AS RowNumber
    FROM TableName
    UNION ALL
    SELECT r.ColumnName + ',' + t.ColumnName, t.RowNumber
    FROM RecursiveCTE r
    INNER JOIN TableName t ON t.RowNumber = r.RowNumber + 1
)
SELECT TOP 1 ColumnName
FROM RecursiveCTE
ORDER BY RowNumber DESC
OPTION (MAXRECURSION 0)

Please note that the above method is applicable for SQL Server 2017 or higher versions. In earlier versions, you may need to use different methods to achieve the merging of multiple rows into one.

bannerAds