Como usar o grupo para concatenar strings no servidor SQL?
Como consigo:
id Name Value
1 A 4
1 B 8
2 C 9
a
id Column
1 A:4, B:8
2 C:9
16 answers
Não é necessário nenhum CURSOR, enquanto o ciclo ou função definida pelo Utilizador .
Só preciso de ser criativo para XML e PATH.
[Nota: Esta solução só funciona em SQL 2005 e posteriormente. A pergunta Original não especificou a versão em uso.]
CREATE TABLE #YourTable ([ID] INT, [Name] CHAR(1), [Value] INT)
INSERT INTO #YourTable ([ID],[Name],[Value]) VALUES (1,'A',4)
INSERT INTO #YourTable ([ID],[Name],[Value]) VALUES (1,'B',8)
INSERT INTO #YourTable ([ID],[Name],[Value]) VALUES (2,'C',9)
SELECT
[ID],
STUFF((
SELECT ', ' + [Name] + ':' + CAST([Value] AS VARCHAR(MAX))
FROM #YourTable
WHERE (ID = Results.ID)
FOR XML PATH(''),TYPE).value('(./text())[1]','VARCHAR(MAX)')
,1,2,'') AS NameValues
FROM #YourTable Results
GROUP BY ID
DROP TABLE #YourTable
Usar o caminho XML não irá concatenar perfeitamente como seria de esperar... ele irá substituir " & " por " & " e também irá mexer com <" and ">
...talvez mais algumas coisas, Não tenho a certeza...mas podes tentar isto.
Encontrei uma solução para isto... tem de substituir:
FOR XML PATH('')
)
Com:
FOR XML PATH(''),TYPE
).value('(./text())[1]','VARCHAR(MAX)')
...ou NVARCHAR(MAX)
Se é isso que estás a usar.
&
, <
, >
) que foram codificadas.
A versão final do meu código (que não responde à pergunta original, mas pode ser útil para alguém) é assim:
CREATE TABLE #YourTable ([ID] INT, [Name] VARCHAR(MAX), [Value] INT)
INSERT INTO #YourTable ([ID],[Name],[Value]) VALUES (1,'Oranges & Lemons',4)
INSERT INTO #YourTable ([ID],[Name],[Value]) VALUES (1,'1 < 2',8)
INSERT INTO #YourTable ([ID],[Name],[Value]) VALUES (2,'C',9)
SELECT [ID],
STUFF((
SELECT ', ' + CAST([Name] AS VARCHAR(MAX))
FROM #YourTable WHERE (ID = Results.ID)
FOR XML PATH(''),TYPE
/* Use .value to uncomment XML entities e.g. > < etc*/
).value('.','VARCHAR(MAX)')
,1,2,'') as NameValues
FROM #YourTable Results
GROUP BY ID
DROP TABLE #YourTable
Em vez de usar um espaço como delimitador e substituir todos os espaços por vírgulas, ele apenas pré-pende uma vírgula e espaço para cada valor, em seguida, usa STUFF
para remover os dois primeiros caracteres.
A codificação XML é tratada automaticamente utilizando a Directiva Tipo.
Se for SQL Server 2017 ou SQL Server vNext, SQL Azure você pode usar string_agg como abaixo:
select id, string_agg(concat(name, ':', [value]), ', ')
from #YourTable
group by id
Outra opção usando o servidor sql 2005 e acima
---- test data
declare @t table (OUTPUTID int, SCHME varchar(10), DESCR varchar(10))
insert @t select 1125439 ,'CKT','Approved'
insert @t select 1125439 ,'RENO','Approved'
insert @t select 1134691 ,'CKT','Approved'
insert @t select 1134691 ,'RENO','Approved'
insert @t select 1134691 ,'pn','Approved'
---- actual query
;with cte(outputid,combined,rn)
as
(
select outputid, SCHME + ' ('+DESCR+')', rn=ROW_NUMBER() over (PARTITION by outputid order by schme, descr)
from @t
)
,cte2(outputid,finalstatus,rn)
as
(
select OUTPUTID, convert(varchar(max),combined), 1 from cte where rn=1
union all
select cte2.outputid, convert(varchar(max),cte2.finalstatus+', '+cte.combined), cte2.rn+1
from cte2
inner join cte on cte.OUTPUTID = cte2.outputid and cte.rn=cte2.rn+1
)
select outputid, MAX(finalstatus) from cte2 group by outputid
Instalar os agregados SQLCLR de http://groupconcat.codeplex.com
Então você pode escrever um código como este para obter o resultado que você pediu:
CREATE TABLE foo
(
id INT,
name CHAR(1),
Value CHAR(1)
);
INSERT INTO dbo.foo
(id, name, Value)
VALUES (1, 'A', '4'),
(1, 'B', '8'),
(2, 'C', '9');
SELECT id,
dbo.GROUP_CONCAT(name + ':' + Value) AS [Column]
FROM dbo.foo
GROUP BY id;
SQL Server 2005 e mais tarde permitir - lhe-á criar as suas próprias funções agregadas personalizadas, incluindo para coisas como a concatenação-veja a amostra na parte inferior do artigo ligado.
Referência: https://msdn.microsoft.com/en-us/library/mt775028.aspx
Só para adicionar ao que o Cade disse, isto é normalmente uma coisa de exibição frontal e deve, portanto, ser manuseado lá. Eu sei que às vezes é mais fácil escrever algo 100% em SQL para coisas como exportação de arquivos ou outras soluções "SQL apenas", mas na maioria das vezes esta concatenação deve ser tratada em sua camada de exibição.
No Oracle você pode usar a função agregada LISTAGG. Um exemplo seria:
name type
------------
name1 type1
name2 type2
name2 type3
SELECT name, LISTAGG(type, '; ') WITHIN GROUP(ORDER BY name)
FROM table
GROUP BY name
Resultaria em:
name type
------------
name1 type1
name2 type2; type3
Este tipo de pergunta é feita aqui muitas vezes, e a solução vai depender muito dos Requisitos subjacentes:
Https://stackoverflow.com/search?q=sql+pivô
E
Https://stackoverflow.com/search?q=sql + concatenato
Normalmente, não existe uma maneira SQL-só de o fazer sem SQL dinâmico, uma função definida pelo utilizador ou um cursor.
CREATE TABLE #YourTable ( [ID] INT, [Name] CHAR(1), [Value] INT )
INSERT INTO #YourTable ([ID], [Name], [Value]) VALUES (1, 'A', 4)
INSERT INTO #YourTable ([ID], [Name], [Value]) VALUES (1, 'B', 8)
INSERT INTO #YourTable ([ID], [Name], [Value]) VALUES (2, 'C', 9)
SELECT [ID],
REPLACE(REPLACE(REPLACE(
(SELECT [Name] + ':' + CAST([Value] AS VARCHAR(MAX)) as A
FROM #YourTable
WHERE ( ID = Results.ID )
FOR XML PATH (''))
, '</A><A>', ', ')
,'<A>','')
,'</A>','') AS NameValues
FROM #YourTable Results
GROUP BY ID
DROP TABLE #YourTable
Não preciso de um cursor... um laço while é suficiente.
------------------------------
-- Setup
------------------------------
DECLARE @Source TABLE
(
id int,
Name varchar(30),
Value int
)
DECLARE @Target TABLE
(
id int,
Result varchar(max)
)
INSERT INTO @Source(id, Name, Value) SELECT 1, 'A', 4
INSERT INTO @Source(id, Name, Value) SELECT 1, 'B', 8
INSERT INTO @Source(id, Name, Value) SELECT 2, 'C', 9
------------------------------
-- Technique
------------------------------
INSERT INTO @Target (id)
SELECT id
FROM @Source
GROUP BY id
DECLARE @id int, @Result varchar(max)
SET @id = (SELECT MIN(id) FROM @Target)
WHILE @id is not null
BEGIN
SET @Result = null
SELECT @Result =
CASE
WHEN @Result is null
THEN ''
ELSE @Result + ', '
END + s.Name + ':' + convert(varchar(30),s.Value)
FROM @Source s
WHERE id = @id
UPDATE @Target
SET Result = @Result
WHERE id = @id
SET @id = (SELECT MIN(id) FROM @Target WHERE @id < id)
END
SELECT *
FROM @Target
SELECT stuff(
(
select ', ' + x from (SELECT 'xxx' x union select 'yyyy') tb
FOR XML PATH('')
)
, 1, 2, '')
Substitui esta linha:
select ', ' + x from (SELECT 'xxx' x union select 'yyyy') tb
Com a tua pergunta.
Não vi nenhuma Cruz aplicar respostas, também não há necessidade de extração xml. Aqui está uma versão ligeiramente diferente do que Kevin Fairchild escreveu. É mais rápido e fácil de usar em consultas mais complexas:
select T.ID
,MAX(X.cl) NameValues
from #YourTable T
CROSS APPLY
(select STUFF((
SELECT ', ' + [Name] + ':' + CAST([Value] AS VARCHAR(MAX))
FROM #YourTable
WHERE (ID = T.ID)
FOR XML PATH(''))
,1,2,'') [cl]) X
GROUP BY T.ID
Pode melhorar significativamente o desempenho da seguinte forma se o grupo by contiver principalmente um item:
SELECT
[ID],
CASE WHEN MAX( [Name]) = MIN( [Name]) THEN
MAX( [Name]) NameValues
ELSE
STUFF((
SELECT ', ' + [Name] + ':' + CAST([Value] AS VARCHAR(MAX))
FROM #YourTable
WHERE (ID = Results.ID)
FOR XML PATH(''),TYPE).value('(./text())[1]','VARCHAR(MAX)')
,1,2,'') AS NameValues
END
FROM #YourTable Results
GROUP BY ID