Como concatenar o texto de várias linhas para uma única cadeia de texto no servidor SQL?
considere uma tabela de bases de dados com nomes, com três linhas:
Peter
Paul
Mary
Há uma maneira fácil de transformar isto numa única corda de Peter, Paul, Mary
?
30 answers
Se estiver no SQL Server 2017 ou Azure, veja Mathieu Renda answer .
Tive um problema semelhante quando estava a tentar juntar-me a duas mesas com um para muitos relacionamentos. In SQL 2005 I found thatXML PATH
method can handle the concatenation of the rows very easily.
Se houver uma tabela chamada STUDENTS
SubjectID StudentName
---------- -------------
1 Mary
1 John
1 Sam
2 Alaina
2 Edward
O resultado que eu esperava era:
SubjectID StudentName
---------- -------------
1 Mary, John, Sam
2 Alaina, Edward
Usei o seguinte T-SQL
:
SELECT Main.SubjectID,
LEFT(Main.Students,Len(Main.Students)-1) As "Students"
FROM
(
SELECT DISTINCT ST2.SubjectID,
(
SELECT ST1.StudentName + ',' AS [text()]
FROM dbo.Students ST1
WHERE ST1.SubjectID = ST2.SubjectID
ORDER BY ST1.SubjectID
FOR XML PATH ('')
) [Students]
FROM dbo.Students ST2
) [Main]
Podes fazer a mesma coisa de uma forma mais compacta, se conseguires. concate as vírgulas no início e use substring
para saltar o primeiro para que você não precise fazer uma sub-consulta:
SELECT DISTINCT ST2.SubjectID,
SUBSTRING(
(
SELECT ','+ST1.StudentName AS [text()]
FROM dbo.Students ST1
WHERE ST1.SubjectID = ST2.SubjectID
ORDER BY ST1.SubjectID
FOR XML PATH ('')
), 2, 1000) [Students]
FROM dbo.Students ST2
Esta resposta pode retornar resultados inesperados quando uma ordem por cláusula está presente. Para resultados consistentes, use um dos métodos de caminho para XML detalhados em outras respostas.
Utilizar COALESCE
:
DECLARE @Names VARCHAR(8000)
SELECT @Names = COALESCE(@Names + ', ', '') + Name
FROM People
Apenas uma explicação (uma vez que esta resposta parece obter pontos de vista relativamente regulares):
- O Coalesce é apenas uma fraude útil que realiza duas coisas:
1) não é necessário inicializar @Names
com um valor de texto vazio.
2) não é necessário retirar um separador extra no final.
- A solução acima vai dar resultados incorrectos se uma linha tiver um NULO Nome do valor (se houver um NULO, o NULO vai fazer
@Names
NULL depois que a linha, e a linha seguinte iniciar-se-á como uma cadeia de caracteres vazia novamente. Facilmente fixo com uma de duas soluções:
DECLARE @Names VARCHAR(8000)
SELECT @Names = COALESCE(@Names + ', ', '') + Name
FROM People
WHERE Name IS NOT NULL
Ou:
DECLARE @Names VARCHAR(8000)
SELECT @Names = COALESCE(@Names + ', ', '') +
ISNULL(Name, 'N/A')
FROM People
Dependendo do comportamento que deseja (a primeira opção apenas filtra NULL S fora, a segunda opção mantém-nos na lista com uma mensagem marcada [substitua 'N/A' Pelo que for apropriado para si]).
Um método ainda não mostrado através do XML
data()
o comando no servidor de MS SQL é:
Assume a tabela chamada Lista de nomes com uma coluna chamada FName,
SELECT FName + ', ' AS 'data()'
FROM NameList
FOR XML PATH('')
O Devolve:
"Peter, Paul, Mary, "
Só a vírgula extra deve ser tratada.
Edit: como adoptado do Comentário de @nreilingh, poderá usar o seguinte método para remover a vírgula final. Assumindo os mesmos nomes de tabelas e Colunas:
STUFF(REPLACE((SELECT '#!' + LTRIM(RTRIM(FName)) AS 'data()' FROM NameList
FOR XML PATH('')),' #!',', '), 1, 2, '') as Brands
In SQL Server 2005
SELECT Stuff(
(SELECT N', ' + Name FROM Names FOR XML PATH(''),TYPE)
.value('text()[1]','nvarchar(max)'),1,2,N'')
No servidor SQL 2016
Pode usar o para sintaxe JSON
I. e.
SELECT per.ID,
Emails = JSON_VALUE(
REPLACE(
(SELECT _ = em.Email FROM Email em WHERE em.Person = per.ID FOR JSON PATH)
,'"},{"_":"',', '),'$[0]._'
)
FROM Person per
E o resultado tornar-se-á
Id Emails
1 [email protected]
2 NULL
3 [email protected], [email protected]
Isto irá funcionar até os seus dados contêm caracteres XML inválidos
O '"},{"_":"'
é seguro porque se os seus dados contiverem {[4] } ele será escapado para "},{\"_\":\"
Pode substituir ', '
por qualquer separador de texto
E no servidor SQL 2017, Azure Base de dados SQL
Pode usar a nova função STRING_AGG
SQL Server 2017+ and SQL Azure: STRING_AGG
A partir da próxima versão do servidor SQL, podemos finalmente concatenar através de linhas sem ter que recorrer a qualquer variável ou bruxaria XML.Sem agrupamento
SELECT STRING_AGG(Name, ', ') AS Departments
FROM HumanResources.Department;
Com agrupamento :
SELECT GroupName, STRING_AGG(Name, ', ') AS Departments
FROM HumanResources.Department
GROUP BY GroupName;
Com agrupamento e ordenação
SELECT GroupName, STRING_AGG(Name, ', ') WITHIN GROUP (ORDER BY Name ASC) AS Departments
FROM HumanResources.Department
GROUP BY GroupName;
No MySQL existe uma função, GROUP_CONCAT(), que lhe permite concatenar os valores de várias linhas. Exemplo:
SELECT 1 AS a, GROUP_CONCAT(name ORDER BY name ASC SEPARATOR ', ') AS people
FROM users
WHERE id IN (1,2,3)
GROUP BY a
Uso carvão - Saiba mais aqui
por exemplo:
102
103
104
Depois escreva abaixo do código no servidor sql,
Declare @Numbers AS Nvarchar(MAX) -- It must not be MAX if you have few numbers
SELECT @Numbers = COALESCE(@Numbers + ',', '') + Number
FROM TableName where Number IS NOT NULL
SELECT @Numbers
A saída seria:
102,103,104
Oracle 11g Release 2 suporta a função LISTAGG. Documentação aqui .
COLUMN employees FORMAT A50
SELECT deptno, LISTAGG(ename, ',') WITHIN GROUP (ORDER BY ename) AS employees
FROM emp
GROUP BY deptno;
DEPTNO EMPLOYEES
---------- --------------------------------------------------
10 CLARK,KING,MILLER
20 ADAMS,FORD,JONES,SCOTT,SMITH
30 ALLEN,BLAKE,JAMES,MARTIN,TURNER,WARD
3 rows selected.
Aviso
Tenha cuidado ao implementar esta função se houver possibilidade de a sequência resultante ultrapassar 4000 caracteres. Abrirá uma excepção. Se for esse o caso, então você precisa lidar com a exceção ou rolar sua própria função que impede a string Unida de ir mais de 4000 caracteres.
Criar alguns dados de teste:
postgres=# \c test
You are now connected to database "test" as user "hgimenez".
test=# create table names (name text);
CREATE TABLE
test=# insert into names (name) values ('Peter'), ('Paul'), ('Mary');
INSERT 0 3
test=# select * from names;
name
-------
Peter
Paul
Mary
(3 rows)
Agregar eles em uma matriz:
test=# select array_agg(name) from names;
array_agg
-------------------
{Peter,Paul,Mary}
(1 row)
Converta a lista para um texto delimitado por vírgulas:
test=# select array_to_string(array_agg(name), ', ') from names;
array_to_string
-------------------
Peter, Paul, Mary
(1 row)
Feito
Desde o PostgreSQL 9.0 é ainda mais fácil .
No servidor sql 2005 e mais tarde, use a consulta abaixo para concatenar as linhas.
DECLARE @t table
(
Id int,
Name varchar(10)
)
INSERT INTO @t
SELECT 1,'a' UNION ALL
SELECT 1,'b' UNION ALL
SELECT 2,'c' UNION ALL
SELECT 2,'d'
SELECT ID,
stuff(
(
SELECT ','+ [Name] FROM @t WHERE Id = t.Id FOR XML PATH('')
),1,1,'')
FROM (SELECT DISTINCT ID FROM @t ) t
Eu Não tenho acesso a um servidor SQL em casa, por isso estou a adivinhar a sintaxe aqui, mas é mais ou menos:
DECLARE @names VARCHAR(500)
SELECT @names = @names + ' ' + Name
FROM Names
Foi sugerida uma solução de CTE recursiva, mas não foi fornecido Nenhum Código. O código abaixo é um exemplo de um CTE recursivo -- note que, embora os resultados correspondam à pergunta, os dados não completamente correspondem à descrição dada, pois assumo que você realmente quer fazer isto em grupos de linhas, nem todas as linhas na tabela. Alterá-lo para corresponder a todas as linhas da tabela é deixado como um exercício para o leitor.
;with basetable as
( SELECT id, CAST(name as varchar(max))name,
ROW_NUMBER() OVER(Partition By id order by seq) rw,
COUNT(*) OVER (Partition By id) recs
FROM (VALUES (1, 'Johnny', 1), (1,'M', 2),
(2,'Bill', 1), (2, 'S.', 4), (2, 'Preston', 5), (2, 'Esq.', 6),
(3, 'Ted', 1), (3,'Theodore', 2), (3,'Logan', 3),
(4, 'Peter', 1), (4,'Paul', 2), (4,'Mary', 3)
)g(id, name, seq)
),
rCTE as (
SELECT recs, id, name, rw from basetable where rw=1
UNION ALL
SELECT b.recs, r.ID, r.name +', '+ b.name name, r.rw+1
FROM basetable b
inner join rCTE r
on b.id = r.id and b.rw = r.rw+1
)
SELECT name FROM rCTE
WHERE recs = rw and ID=4
Começando com o PostgreSQL 9.0 isto é bastante simples:
select string_agg(name, ',')
from names;
Nas versões anteriores a 9, 0 array_agg()
pode ser usado como mostrado por hgmnz
Você precisa criar uma variável que irá manter o seu resultado final e selecioná-lo, assim.
A Solução Mais Fácil
DECLARE @char VARCHAR(MAX);
SELECT @char = COALESCE(@char + ', ' + [column], [column])
FROM [table];
PRINT @char;
No servidor de SQL vNext isto será incorporado com a função STRING_AGG, leia mais sobre isso aqui: https://msdn.microsoft.com/en-us/library/mt790580.aspx
Usar o XML ajudou-me a separar as linhas com vírgulas. Para a vírgula extra podemos usar a função de substituição do servidor SQL. Em vez de adicionar uma vírgula, O uso do AS 'data()' irá concatenar as linhas com espaços, que mais tarde podem ser substituídos por vírgulas como a sintaxe escrita abaixo.
REPLACE(
(select FName AS 'data()' from NameList for xml path(''))
, ' ', ', ')
Uma solução pronta a usar, sem vírgulas extra:
select substring(
(select ', '+Name AS 'data()' from Names for xml path(''))
,3, 255) as "MyList"
Uma lista vazia resultará em valor nulo. Normalmente você irá inserir a lista em uma coluna de tabela ou variável de programa: ajuste o comprimento máximo 255 para sua necessidade.
(Diwakar e Jens Frandsen deram boas respostas, mas precisam de melhorias.)
DECLARE @Names VARCHAR(8000)
SELECT @name = ''
SELECT @Names = @Names + ',' + Names FROM People
SELECT SUBSTRING(2, @Names, 7998)
Isto coloca a vírgula perdida no início.
No entanto, se necessitar de outras colunas, ou de CSV uma tabela-filhos, terá de a embrulhar num campo definido pelo utilizador escalar (UDF).
Também pode usar o XML path como uma sub-pesquisa correlacionada na cláusula de selecção (mas eu teria de esperar até voltar ao trabalho porque o Google não faz coisas de trabalho em casa : -)
Abaixo está um exemplo que usa o servidor SQL "Information_Schema.Colunas " mesa. Usando esta solução, não é necessário criar tabelas ou adicionar dados. Este exemplo cria uma lista separada por vírgulas de nomes de colunas para todas as tabelas da base de dados.
SELECT
Table_Name
,STUFF((
SELECT ',' + Column_Name
FROM INFORMATION_SCHEMA.Columns Columns
WHERE Tables.Table_Name = Columns.Table_Name
ORDER BY Column_Name
FOR XML PATH ('')), 1, 1, ''
)Columns
FROM INFORMATION_SCHEMA.Columns Tables
GROUP BY TABLE_NAME
SELECT STUFF((SELECT ', ' + name FROM [table] FOR XML PATH('')), 1, 2, '')
Aqui está uma amostra.
DECLARE @t TABLE (name VARCHAR(10))
INSERT INTO @t VALUES ('Peter'), ('Paul'), ('Mary')
SELECT STUFF((SELECT ', ' + name FROM @t FOR XML PATH('')), 1, 2, '')
--Peter, Paul, Mary
Para o Oracle DBs, veja esta pergunta: Como podem várias linhas ser concatenadas em uma no Oracle sem criar um procedimento armazenado?
A melhor resposta parece ser por @Emmanuel, usando a função listagg () incorporada, disponível no Oracle 11g Release 2 e mais tarde.
SELECT question_id,
LISTAGG(element_id, ',') WITHIN GROUP (ORDER BY element_id)
FROM YOUR_TABLE;
GROUP BY question_id
Como @user762952 salientou, e de acordo com a documentação da Oracle http://www.oracle-base.com/articles/misc/string-aggregation-techniques.php , a função WM_CONCAT () também é um Opcao. Parece estável, mas Oracle recomenda explicitamente contra usá-lo para qualquer aplicação SQL, então use em seu próprio risco.
Além disso, você terá que escrever sua própria função; o documento Oracle acima tem um guia sobre como fazer isso.
Para evitar valores nulos pode utilizar CONCAT ()
DECLARE @names VARCHAR(500)
SELECT @names = CONCAT(@names, ' ', name)
FROM Names
select @names
Esta resposta exigirá algum privilégio no servidor para funcionar.
Se você quiser, eu já criei o conjunto, e é possível baixar o DLL Aqui.
Depois de o ter baixado, terá de executar o seguinte programa no seu servidor SQL:
CREATE Assembly concat_assembly
AUTHORIZATION dbo
FROM '<PATH TO Concat.dll IN SERVER>'
WITH PERMISSION_SET = SAFE;
GO
CREATE AGGREGATE dbo.concat (
@Value NVARCHAR(MAX)
, @Delimiter NVARCHAR(4000)
) RETURNS NVARCHAR(MAX)
EXTERNAL Name concat_assembly.[Concat.Concat];
GO
sp_configure 'clr enabled', 1;
RECONFIGURE
Observe que o caminho para a montagem pode ser acessível ao servidor. Uma vez que você fez com sucesso todos os passos, você pode usar a função como:
SELECT dbo.Concat(field1, ',')
FROM Table1
Espero que ajude!!!
Exemplo completo do MySQL:
Temos utilizadores que podem ter muitos dados e queremos ter uma saída, onde podemos ver todos os dados dos utilizadores numa lista:
Resultado:
___________________________
| id | rowList |
|-------------------------|
| 0 | 6, 9 |
| 1 | 1,2,3,4,5,7,8,1 |
|_________________________|
Configuração Da Tabela:
CREATE TABLE `Data` (
`id` int(11) NOT NULL,
`user_id` int(11) NOT NULL
) ENGINE=InnoDB AUTO_INCREMENT=11 DEFAULT CHARSET=latin1;
INSERT INTO `Data` (`id`, `user_id`) VALUES
(1, 1),
(2, 1),
(3, 1),
(4, 1),
(5, 1),
(6, 0),
(7, 1),
(8, 1),
(9, 0),
(10, 1);
CREATE TABLE `User` (
`id` int(11) NOT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1;
INSERT INTO `User` (`id`) VALUES
(0),
(1);
Pesquisa:
SELECT User.id, GROUP_CONCAT(Data.id ORDER BY Data.id) AS rowList FROM User LEFT JOIN Data ON User.id = Data.user_id GROUP BY User.id
DECLARE @names VARCHAR(MAX)
SET @names = ''
SELECT @names = @names + ', ' + Name FROM Names
-- Deleting last two symbols (', ')
SET @sSql = LEFT(@sSql, LEN(@sSql) - 1)
Normalmente uso a opção deste modo para concatenar cadeias de caracteres no servidor SQL:
with lines as
(
select
row_number() over(order by id) id, -- id is a line id
line -- line of text.
from
source -- line source
),
result_lines as
(
select
id,
cast(line as nvarchar(max)) line
from
lines
where
id = 1
union all
select
l.id,
cast(r.line + N', ' + l.line as nvarchar(max))
from
lines l
inner join
result_lines r
on
l.id = r.id + 1
)
select top 1
line
from
result_lines
order by
id desc
Isto também pode ser útil
create table #test (id int,name varchar(10))
--use separate inserts on older versions of SQL Server
insert into #test values (1,'Peter'), (1,'Paul'), (1,'Mary'), (2,'Alex'), (3,'Jack')
DECLARE @t VARCHAR(255)
SELECT @t = ISNULL(@t + ',' + name, name) FROM #test WHERE id = 1
select @t
drop table #test
O Devolve
Peter,Paul,Mary
Se quiser lidar com nulls, pode fazê-lo adicionando uma cláusula onde ou adicionando outra COALESCE em torno da primeira.
DECLARE @Names VARCHAR(8000)
SELECT @Names = COALESCE(COALESCE(@Names + ', ', '') + Name, @Names) FROM People
--SQL Server 2005+
CREATE TABLE dbo.Students
(
StudentId INT
, Name VARCHAR(50)
, CONSTRAINT PK_Students PRIMARY KEY (StudentId)
);
CREATE TABLE dbo.Subjects
(
SubjectId INT
, Name VARCHAR(50)
, CONSTRAINT PK_Subjects PRIMARY KEY (SubjectId)
);
CREATE TABLE dbo.Schedules
(
StudentId INT
, SubjectId INT
, CONSTRAINT PK__Schedule PRIMARY KEY (StudentId, SubjectId)
, CONSTRAINT FK_Schedule_Students FOREIGN KEY (StudentId) REFERENCES dbo.Students (StudentId)
, CONSTRAINT FK_Schedule_Subjects FOREIGN KEY (SubjectId) REFERENCES dbo.Subjects (SubjectId)
);
INSERT dbo.Students (StudentId, Name) VALUES
(1, 'Mary')
, (2, 'John')
, (3, 'Sam')
, (4, 'Alaina')
, (5, 'Edward')
;
INSERT dbo.Subjects (SubjectId, Name) VALUES
(1, 'Physics')
, (2, 'Geography')
, (3, 'French')
, (4, 'Gymnastics')
;
INSERT dbo.Schedules (StudentId, SubjectId) VALUES
(1, 1) --Mary, Physics
, (2, 1) --John, Physics
, (3, 1) --Sam, Physics
, (4, 2) --Alaina, Geography
, (5, 2) --Edward, Geography
;
SELECT
sub.SubjectId
, sub.Name AS [SubjectName]
, ISNULL( x.Students, '') AS Students
FROM
dbo.Subjects sub
OUTER APPLY
(
SELECT
CASE ROW_NUMBER() OVER (ORDER BY stu.Name) WHEN 1 THEN '' ELSE ', ' END
+ stu.Name
FROM
dbo.Students stu
INNER JOIN dbo.Schedules sch
ON stu.StudentId = sch.StudentId
WHERE
sch.SubjectId = sub.SubjectId
ORDER BY
stu.Name
FOR XML PATH('')
) x (Students)
;