How to concatenate lines?

Asked

Viewed 4,174 times

7

How to concatenate all the results of a query in SQL Server in order to return all information in one variable?

For example, considering a table cliente with the following composition:

╔═══╦════════════╗
║   ║ Nome       ║
╠═══╬════════════╣
║ 1 ║ José       ║
║ 2 ║ Luis       ║
║ 3 ║ Antônio    ║
╚═══╩════════════╝

The desired result would be:

José, Luis, Antonio
sql database sql-server

1 answer

10


One way (prior to SQL 2012) is to use a variable to concatenate the row results as follows:

DECLARE @texto varchar(max);

SELECT @texto = ISNULL(@texto + ', ', '') + cli.nome
  FROM cliente cli;

print @texto;

Where the ISNULL ensures that the comma will only be inserted if there is previous value assigned to the variable (in case starting to place the comma only after the José).

Another way is to use the syntax FOR XML PATH(''):

SELECT DISTINCT SUBSTRING((SELECT ',' + cli.nome AS [text()]
                             FROM cliente cli
                              FOR XML PATH ('')), 2, 8000) AS texto
  FROM cliente cli2

From the version 2017 of SQL Server you can use the function STRING_AGG:

SELECT STRING_AGG(cli.nome, ',') AS texto
  FROM cliente cli;

STRING_AGG

Concatenate the values of the string expressions and place the separator values between them. Separator is not added at the end of the string.

References:

Browser other questions tagged sql database sql-server

You are not signed in. Login or sign up in order to post.