Início / dba / Perguntas / 68089
Accepted
user2732180
Asked: 2014-06-12 17:28:44 +0800 CST

Como usar COALESCE com várias linhas e sem vírgula precedente?

  • 772

Estou tentando alcançar o seguinte:

California | Los Angeles, San Francisco, Sacramento
Florida    | Jacksonville, Miami

Infelizmente, estou recebendo ", Los Angeles, San Francisco, Sacramento, Jacksonville, Miami"

Eu posso alcançar meus resultados desejados usando a função STUFF, mas queria saber se existe uma maneira mais limpa de fazer isso usando COALESCE?

STATE       | CITY
California  | San Francisco
California  | Los Angeles
California  | Sacramento
Florida     | Miami
Florida     | Jacksonville 


DECLARE @col NVARCHAR(MAX);
SELECT @col= COALESCE(@col, '') + ',' + city
FROM tbl where city = 'California';
SELECT @col;

Obrigado

sql-server t-sql

2 respostas

  1. Best Answer

    Esta pode ser a abordagem mais limpa que você procura. Basicamente, verifique se a variável já foi inicializada. Se não tiver, defina-o para a string vazia e anexe a primeira cidade (sem vírgula à esquerda). Se tiver, acrescente uma vírgula e acrescente a cidade.

    DECLARE @col nvarchar(MAX);
SELECT @col = COALESCE(@col + ',', '') + city
  FROM dbo.tbl WHERE state = 'California';

    Claro, isso só funciona para preencher uma variável por estado. Se você estiver puxando a lista para cada estado, um de cada vez, há uma solução melhor de uma só vez:

    SELECT [state], cities = STUFF((
    SELECT N', ' + city FROM dbo.tbl
    WHERE [state] = x.[state]
    FOR XML PATH(''), TYPE).value(N'.[1]', N'nvarchar(max)'), 1, 2, N'')
FROM dbo.tbl AS x
GROUP BY [state]
ORDER BY [state];

    Resultados:

    state       cities
----------  --------------------------------------
California  San Francisco, Los Angeles, Sacramento  
Florida     Miami, Jacksonville

    Para ordenar pelo nome da cidade dentro de cada estado:

    SELECT [state], cities = STUFF((
    SELECT N', ' + city FROM dbo.tbl
    WHERE [state] = x.[state]
    ORDER BY city
    FOR XML PATH(''), TYPE).value(N'.[1]', N'nvarchar(max)'), 1, 2, N'')
FROM dbo.tbl AS x
GROUP BY [state]
ORDER BY [state];

    No Banco de Dados SQL do Azure ou SQL Server 2017+, você pode usar a nova STRING_AGG()função :

    SELECT [state], cities = STRING_AGG(city, N', ')
  FROM dbo.tbl
  GROUP BY [state]
  ORDER BY [state];

    E ordenados pelo nome da cidade:

    SELECT [state], cities = STRING_AGG(city, N', ') 
                         WITHIN GROUP (ORDER BY city)
  FROM dbo.tbl
  GROUP BY [state]
  ORDER BY [state];
    • 48

  2. Apenas para adicionar à resposta de Aaron acima ...

    Esteja ciente de que um ORDER BYpode quebrar ao incluir apenas o último item em sua consulta. No meu caso, eu não estava agrupando, então não tenho certeza se isso faz diferença. Estou usando o SQL 2014. No meu caso, tenho algo como valor1, valor2, valor3... mas meu resultado na variável foi apenas valor3.

    Aaron comentou para dizer:

    Isso foi relatado pelo menos quatro vezes no Connect:

    1. Em concatenação de variáveis ​​e ordenação por resultados de filtros (como onde condição)
    2. (n) a construção de varchar do ResultSet falha quando ORDER BY é adicionado
    3. Atribuir uma variável local de um SELECT ordenado com CROSS APPLYs e uma função com valor de tabela retorna apenas o último valor
    4. Ao concatenar valores varchar(max)/nvarchar(max) de uma variável de tabela, resultados incorretos podem ser retornados se filtrar e ordenar por uma coluna de chave não primária

    Exemplo de resposta da Microsoft:

    O comportamento que você está vendo é por design. O uso de operações de atribuição (concatenação neste exemplo) em consultas com a cláusula ORDER BY tem um comportamento indefinido.

    A resposta também faz referência ao KB 287515:

    PRB: Plano de execução e resultados de consultas de concatenação agregada dependem da localização da expressão

    A solução é usar FOR XML PATH(a segunda abordagem na resposta de Aaron) se a ordem de concatenação for importante e, é claro, se você quiser incluir todos os valores. Veja também:

    Concatenação de nvarchar / índice / comportamento inexplicável de nvarchar (max) no Stack Overflow

    • 8

relate perguntas