STRING_AGG (Transact-SQL)
Aplica-se a:
SQL Server 2017 (14.x) e posterior Banco de Dados SQL do Azure Instância Gerenciada de SQL do Azure Azure Synapse Analytics Ponto de extremidade de análise de SQL no Microsoft Fabric Warehouse no Microsoft Fabric
Concatena os valores das expressões de cadeia de caracteres e coloca os valores de separador entre eles. O separador não é adicionado ao final da cadeia de caracteres.
Convenções de sintaxe de Transact-SQL
Sintaxe
STRING_AGG ( expression, separator ) [ <order_clause> ]
<order_clause> ::=
WITHIN GROUP ( ORDER BY <order_by_expression_list> [ ASC | DESC ] )
Argumentos
expressão
É uma expressão de qualquer tipo. As expressões são convertidas em tipos NVARCHAR ou VARCHAR durante a concatenação. Tipos que não são uma cadeia de caracteres são convertidos no tipo NVARCHAR.
separator
É uma expression do tipo NVARCHAR ou VARCHAR que é usada como separador de cadeias de caracteres concatenadas. Pode ser um literal ou uma variável.
<order_clause>
Opcionalmente, especifique a ordem dos resultados concatenados usando a cláusula WITHIN GROUP:
WITHIN GROUP ( ORDER BY <order_by_expression_list> [ ASC | DESC ] )
<order_by_expression_list>
Uma lista de expressions de não constante que pode ser usada para classificar os resultados. Apenas uma order_by_expression é permitida por consulta. A ordem de classificação padrão é crescente.
Tipos de retorno
O tipo de retorno depende do primeiro argumento (expressão). Se o argumento de entrada for um tipo de cadeia de caracteres (NVARCHAR, VARCHAR), o tipo de resultado será o mesmo do tipo de entrada. A seguinte tabela lista as conversões automáticas:
| Tipo de expressão de entrada | Result |
|---|---|
| NVARCHAR(MAX) | NVARCHAR(MAX) |
| VARCHAR(MAX) | VARCHAR(MAX) |
| NVARCHAR(1...4000) | NVARCHAR(4000) |
| VARCHAR(1...8000) | VARCHAR(8000) |
| int, bigint, smallint, tinyint, numeric, float, real, bit, decimal, smallmoney, money, datetime e datetime2 | NVARCHAR(4000) |
Comentários
STRING_AGG é uma função de agregação que usa todas as expressões de linhas e concatena-as em uma única cadeia de caracteres. Os valores de expressão são convertidos implicitamente em tipos de cadeia de caracteres e depois concatenados. A conversão implícita em cadeias de caracteres segue as regras existentes para conversões de tipo de dados. Para obter mais informações sobre conversões de tipo de dados, consulte CAST e CONVERT (Transact-SQL).
Se a expressão de entrada for um tipo VARCHAR, o separador não poderá ser um tipo NVARCHAR.
Valores nulos são ignorados e o separador correspondente não é adicionado. Para retornar um espaço reservado para valores nulos, use a função ISNULL, conforme demonstrado no exemplo B.
STRING_AGG está disponível em qualquer nível de compatibilidade.
Observação
O <order_clause> está disponível com o nível de compatibilidade do banco de dados 110 e superior.
Exemplos
A maioria dos exemplos neste artigo faz referência aos bancos de dados de exemplo do AdventureWorks.
a. Gerar a lista de nomes separados em novas linhas
O exemplo a seguir gera uma lista de nomes em uma única célula de resultados, separados com retornos de carro.
USE AdventureWorks2022;
GO
SELECT STRING_AGG (CONVERT(NVARCHAR(max),FirstName), CHAR(13)) AS csv
FROM Person.Person;
GO
Veja a seguir o conjunto de resultados.
| csv |
|---|
| Syed Catherine Kim Kim Kim Hazem ... |
Os valores NULL encontrados nas células name não são retornados no resultado.
Observação
Se usar o Editor de Consultas do SQL Server Management Studio, a opção Resultados em Grade não poderá implementar o retorno de carro. Alterne para Resultados em Texto para ver o conjunto de resultados corretamente.
Os Resultados em Texto são truncados para 256 caracteres por padrão. Para aumentar esse limite, altere a opção Número máximo de caracteres exibidos em cada coluna.
B. Gerar uma lista de nomes separados por vírgula sem valores NULL
O exemplo a seguir substitui valores nulos por 'N/A' e retorna os nomes separados por vírgulas em uma única célula de resultados.
USE AdventureWorks2022;
GO
SELECT STRING_AGG(CONVERT(NVARCHAR(max), ISNULL(FirstName,'N/A')), ',') AS csv
FROM Person.Person;
GO
Veja a seguir o conjunto de resultados.
Observação
Os resultados são mostrados como cortados.
| csv |
|---|
| Syed,Catherine,Kim,Kim,Kim,Hazem,Sam,Humberto,Gustavo,Pilar,Pilar, ... |
C. Gerar valores separados por vírgula
USE AdventureWorks2022;
GO
SELECT STRING_AGG(CONVERT(NVARCHAR(max), CONCAT(FirstName, ' ', LastName, '(', ModifiedDate, ')')), CHAR(13)) AS names
FROM Person.Person;
GO
Veja a seguir o conjunto de resultados.
Observação
Os resultados são mostrados como cortados.
| nomes |
|---|
| Ken Sánchez (8 de fevereiro de 2003, 12h) Terri Duffy (24 de fevereiro de 2002, 12h) Roberto Tamburello (5 de dezembro de 2001, 12h) Rob Walters (29 de dezembro de 2001, 12h) ... |
Observação
Se você estiver usando o Editor de Consultas do Management Studio, a opção Resultados em Grade não poderá implementar o retorno de carro. Alterne para Resultados em Texto para ver o conjunto de resultados corretamente.
D. Retornar artigos de notícias com marcas relacionadas
Imagine um banco de dados em que artigos e suas marcas sejam separados em tabelas diferentes. Um desenvolvedor deseja retornar uma linha por artigo com todas as marcas associadas. A seguinte consulta atinge este resultado:
SELECT a.articleId, title, STRING_AGG (tag, ',') as tags
FROM dbo.Article AS a
LEFT JOIN dbo.ArticleTag AS t
ON a.ArticleId = t.ArticleId
GROUP BY a.articleId, title;
GO
Veja a seguir o conjunto de resultados.
| articleId | título | marcas |
|---|---|---|
| 172 | Pesquisas indicam resultados aproximados da eleição | política, pesquisas, câmara municipal |
| 176 | Previsão de construção de nova estrada para reduzir o congestionamento | NULO |
| 177 | Cachorros continuam sendo mais populares do que gatos | pesquisas, animais |
Observação
A cláusula GROUP BY será necessária se a função STRING_AGG não for o único item na lista SELECT.
E. Gerar uma lista de emails por cidades
A seguinte consulta localiza os endereços de email de funcionários e agrupa-os por cidade:
USE AdventureWorks2022;
GO
SELECT TOP 10 City, STRING_AGG(CONVERT(NVARCHAR(max), EmailAddress), ';') AS emails
FROM Person.BusinessEntityAddress AS BEA
INNER JOIN Person.Address AS A ON BEA.AddressID = A.AddressID
INNER JOIN Person.EmailAddress AS EA ON BEA.BusinessEntityID = EA.BusinessEntityID
GROUP BY City;
GO
Veja a seguir o conjunto de resultados.
Observação
Os resultados são mostrados como cortados.
| City | e-mails |
|---|---|
| Ballard | paige28@adventure-works.com;joshua24@adventure-works.com;javier12@adventure-works.com; ... |
| Baltimore | gilbert9@adventure-works.com |
| Barstow | kristen4@adventure-works.com |
| Basingstoke Hants | dale10@adventure-works.com;heidi9@adventure-works.com |
| Baytown | kelvin15@adventure-works.com |
| Beaverton | billy6@adventure-works.com;dalton35@adventure-works.com;lawrence1@adventure-works.com; ... |
| Bell Gardens | christy8@adventure-works.com |
| Bellevue | min0@adventure-works.com;gigi0@adventure-works.com;terry18@adventure-works.com; ... |
| Bellflower | philip0@adventure-works.com;emma34@adventure-works.com;jorge8@adventure-works.com; ... |
| Bellingham | christopher23@adventure-works.com;frederick7@adventure-works.com;omar0@adventure-works.com; ... |
Os emails retornados na coluna de emails podem ser usados diretamente para enviar emails ao grupo de pessoas que trabalham em algumas cidades específicas.
F. Gerar uma lista classificada de emails por cidades
Semelhante ao exemplo anterior, a seguinte consulta localiza os endereços de email de funcionários, os agrupa por cidade e classifica os emails em ordem alfabética:
USE AdventureWorks2022;
GO
SELECT TOP 10 City, STRING_AGG(CONVERT(NVARCHAR(max), EmailAddress), ';') WITHIN GROUP (ORDER BY EmailAddress ASC) AS Emails
FROM Person.BusinessEntityAddress AS BEA
INNER JOIN Person.Address AS A ON BEA.AddressID = A.AddressID
INNER JOIN Person.EmailAddress AS EA ON BEA.BusinessEntityID = EA.BusinessEntityID
GROUP BY City;
GO
Veja a seguir o conjunto de resultados.
Observação
Os resultados são mostrados como cortados.
| City | Emails |
|---|---|
| Barstow | kristen4@adventure-works.com |
| Basingstoke Hants | dale10@adventure-works.com;heidi9@adventure-works.com |
| Braintree | mindy20@adventure-works.com |
| Bell Gardens | christy8@adventure-works.com |
| Byron | louis37@adventure-works.com |
| Bordeaux | ranjit0@adventure-works.com |
| Carnation | don0@adventure-works.com;douglas0@adventure-works.com;george0@adventure-works.com; ... |
| Boulogne-Billancourt | allen12@adventure-works.com;bethany15@adventure-works.com;carl5@adventure-works.com; ... |
| Berkshire | barbara41@adventure-works.com;brenda4@adventure-works.com;carrie14@adventure-works.com; ... |
| Berks | adriana6@adventure-works.com;alisha13@adventure-works.com;arthur19@adventure-works.com; ... |
Próximas etapas
Saiba mais sobre as funções Transact-SQL nos seguintes artigos: