varios - w3schools concat sql
Como concatenar texto de várias linhas em uma única cadeia de texto no SQL Server? (20)
Considere uma tabela de banco de dados contendo nomes, com três linhas:
Peter
Paul
Mary
Existe uma maneira fácil de transformar isso em uma única sequência de Peter, Paul, Mary
?
Essa resposta pode retornar resultados inesperados quando uma cláusula ORDER BY está presente. Para resultados consistentes, use um dos métodos FOR XML PATH detalhados em outras respostas.
Use COALESCE
:
DECLARE @Names VARCHAR(8000)
SELECT @Names = COALESCE(@Names + ', ', '') + Name
FROM People
Apenas alguma explicação (uma vez que esta resposta parece obter visualizações relativamente regulares):
- Coalesce é realmente apenas uma fraude útil que realiza duas coisas:
1) Não há necessidade de inicializar @Names
com um valor de string vazio.
2) Não há necessidade de retirar um separador extra no final.
- A solução acima fornecerá resultados incorretos se uma linha tiver um valor NULL Name (se houver um NULL , o NULL fará
@Names
NULL após essa linha e a próxima linha será reiniciada como uma string vazia novamente. Facilmente 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 desejado (a primeira opção apenas filtra NULL s, a segunda opção os mantém na lista com uma mensagem de marcador [substitua 'N / A' pelo que for apropriado para você]).
SQL Server 2017+ e SQL Azure: STRING_AGG
Começando com a próxima versão do SQL Server, podemos finalmente concatenar as linhas sem precisar recorrer a nenhuma 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 sub-classificação
SELECT GroupName, STRING_AGG(Name, ', ') WITHIN GROUP (ORDER BY Name ASC) AS Departments
FROM HumanResources.Department
GROUP BY GroupName;
Com as outras respostas, a pessoa que está lendo a resposta deve estar ciente de uma tabela de domínio específica, como veículo ou aluno. A tabela deve ser criada e preenchida com dados para testar uma solução.
Abaixo está um exemplo que usa a tabela "Information_Schema.Columns" do SQL Server. Ao usar essa solução, nenhuma tabela precisa ser criada ou dados adicionados. Este exemplo cria uma lista separada por vírgulas de nomes de colunas para todas as tabelas no banco 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
Começando com o PostgreSQL 9.0, isso é bem simples:
select string_agg(name, ',')
from names;
Em versões anteriores a 9.0 array_agg()
pode ser usado como mostrado por hgmnz
Esse método se aplica ao banco de dados do Teradata Aster apenas porque ele utiliza sua função NPATH.
Mais uma vez, temos alunos de mesa
SubjectID StudentName
---------- -------------
1 Mary
1 John
1 Sam
2 Alaina
2 Edward
Então, com NPATH, é apenas um SELECT:
SELECT * FROM npath(
ON Students
PARTITION BY SubjectID
ORDER BY StudentName
MODE(nonoverlapping)
PATTERN('A*')
SYMBOLS(
'true' as A
)
RESULT(
FIRST(SubjectID of A) as SubjectID,
ACCUMULATE(StudentName of A) as StudentName
)
);
Resultado:
SubjectID StudentName
---------- -------------
1 [John, Mary, Sam]
2 [Alaina, Edward]
Eu costumo usar select como este para concatenar strings no SQL Server:
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
Isso pode ser útil também
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
retorna
Peter,Paul,Mary
No Oracle, é wm_concat
. Eu acredito que esta função está disponível na versão 10g e superior.
No SQL Server 2005 e posterior, 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
O Oracle 11g Release 2 suporta a função LISTAGG. Documentação here .
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 essa função se houver possibilidade de a sequência resultante ultrapassar 4.000 caracteres. Isso lançará uma exceção. Se esse for o caso, você precisará manipular a exceção ou executar sua própria função, evitando que a sequência unida ultrapasse 4000 caracteres.
Para Oracle DBs, veja esta pergunta: Como várias linhas podem ser concatenadas em uma no Oracle sem criar um procedimento armazenado?
A melhor resposta parece ser da @Emmanuel, usando a função LISTAGG () integrada, disponível no Oracle 11g Release 2 e versões posteriores.
SELECT question_id,
LISTAGG(element_id, ',') WITHIN GROUP (ORDER BY element_id)
FROM YOUR_TABLE;
GROUP BY question_id
como @ user762952 apontou, 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 é uma opção. Parece estável, mas a Oracle recomenda explicitamente contra o uso de qualquer aplicativo SQL, portanto, use a seu próprio risco.
Fora isso, você terá que escrever sua própria função; o documento do Oracle acima tem um guia sobre como fazer isso.
Postgres arrays são impressionantes. Exemplo:
Crie 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)
Agregue-os em uma matriz:
test=# select array_agg(name) from names;
array_agg
-------------------
{Peter,Paul,Mary}
(1 row)
Converta a matriz em uma string delimitada por vírgula:
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 .
Se você estiver no SQL Server 2017 ou no Azure, consulte a resposta de Mathieu Renda .
Eu tive um problema semelhante quando estava tentando juntar duas tabelas com relações um-para-muitos. No SQL 2005, descobri que o método XML PATH
pode manipular a concatenação das linhas com muita facilidade.
Se houver uma tabela chamada STUDENTS
SubjectID StudentName
---------- -------------
1 Mary
1 John
1 Sam
2 Alaina
2 Edward
Resultado que eu esperava era:
SubjectID StudentName
---------- -------------
1 Mary, John, Sam
2 Alaina, Edward
Eu 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]
Você pode fazer a mesma coisa de uma forma mais compacta se puder concatenar as vírgulas no início e usar a substring
para pular a primeira, para não precisar fazer uma subconsulta:
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
Um método ainda não mostrado através do comando XML
data()
no MS SQL Server é:
Suponha uma tabela chamada NameList com uma coluna chamada FName,
SELECT FName + ', ' AS 'data()'
FROM NameList
FOR XML PATH('')
retorna:
"Peter, Paul, Mary, "
Apenas a vírgula extra deve ser tratada.
Edit: Como adotado do comentário @ NReilingh, você pode usar o seguinte método para remover a vírgula à direita. 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
Uma solução CTE recursiva foi sugerida, mas nenhum código foi fornecido. O código abaixo é um exemplo de um CTE recursivo - note que, embora os resultados correspondam à pergunta, os dados não correspondem exatamente à descrição dada, pois suponho que você realmente queira fazer isso em grupos de linhas, nem todas 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
Usando o XML ajudou-me a obter linhas separadas por vírgulas. Para a vírgula extra, podemos usar a função replace do SQL Server. Em vez de adicionar uma vírgula, o uso do AS 'data ()' irá concatenar as linhas com espaços, que posteriormente podem ser substituídos por vírgulas como a sintaxe escrita abaixo.
REPLACE(
(select FName AS 'data()' from NameList for xml path(''))
, ' ', ', ')
Use COALESCE - Saiba mais a partir daqui
Por exemplo:
102
103
104
Então escreva abaixo o código no sql server,
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
Saída seria:
102,103,104
MySQL complete Exemplo:
Temos usuários que podem ter muitos dados e queremos ter uma saída, onde podemos ver todos os dados de usuários em uma 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);
Inquerir:
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(8000)
SELECT @name = ''
SELECT @Names = @Names + ',' + Names FROM People
SELECT SUBSTRING(2, @Names, 7998)
Isso coloca a vírgula perdida no começo.
No entanto, se você precisar de outras colunas, ou para uma tabela filha CSV, será necessário agrupar isso em um campo escalar definido pelo usuário (UDF).
Você pode usar o caminho XML como uma subconsulta correlacionada na cláusula SELECT também (mas eu teria que esperar até voltar ao trabalho porque o Google não trabalha em casa :-)