How to concatenate text from multiple rows into a single text string in the SQL Server?
Consider a database table dbo.Students holding student names, with four rows:
StudentName |
---|
Peter |
NULL |
Paul |
Mary |
Query:
DECLARE @StudentNames VARCHAR(MAX) = '';
SELECT @StudentNames = IIF(S.StudentName IS NULL, @StudentNames, IIF(@StudentNames = '', S.StudentName, @StudentNames + ', ' + S.StudentName))
FROM dbo.Students S
Output:
Peter,Paul,Mary
Note: This query handles null values also.
Full Example using table variable:
DECLARE @Students TABLE(StudentName VARCHAR(100))
INSERT INTO @Students(StudentName)
VALUES ('Peter'),
(NULL),
('Paul'),
('Mary')
DECLARE @StudentNames VARCHAR(MAX) = '';
SELECT @StudentNames = IIF(StudentName IS NULL, @StudentNames, IIF(@StudentNames = '', StudentName, @StudentNames + ', ' + StudentName))
FROM @Students
SELECT @StudentNames AS [Student Names]
Output:
Peter,Paul,Mary
SQL Server 2017+ and Azure SQL: STRING_AGG
SELECT STRING_AGG(S.StudentName, ',') AS [Student Names]
FROM @Students S
Output:
Peter,Paul,Mary
With Sorting:
SELECT STRING_AGG(S.StudentName, ',') WITHIN GROUP (ORDER BY S.StudentName) AS [Student Names]
FROM @Students S
Output:
Mary,Paul,Peter
-
CreatedJan 18, 2020
-
UpdatedNov 03, 2020
-
Views1,371
Related Articles
Different types of variables in SQL Server
What is Cross Join in SQL Server
How delete record from parent table when there is Foreign Key relation in SQL Server
What is the differences between CHAR, NCHAR, VARCHAR and NVARCHAR in SQL Server?
How to add or remove a column from a table in SQL Server?
How to insert values into Identity Column in SQL Server?
How to get a fixed-length value in SQL Server?
What is Left Join (or) Left Outer Join in SQL Server?
How to get current week dates in SQL Server?
What are the different types of Date formats in SQL Server?