Jak korzystać z COALESCE z wieloma wierszami i bez poprzedzającego przecinka?

27

Próbuję osiągnąć następujące cele:

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

Niestety dostaję „, Los Angeles, San Francisco, Sacramento, Jacksonville, Miami”

Mogę osiągnąć pożądane wyniki za pomocą funkcji STUFF, ale zastanawiałem się, czy istnieje lepszy sposób na zrobienie tego za pomocą 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;

Dzięki

sql-server t-sql

— użytkownik2732180
źródło

45

To może być czystsze podejście, którego szukasz. Zasadniczo sprawdź, czy zmienna została jeszcze zainicjowana. Jeśli nie, ustaw pusty ciąg znaków i dołącz pierwsze miasto (bez przecinka). Jeśli tak, dodaj przecinek, a następnie dodaj miasto.

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

Oczywiście działa to tylko w przypadku zapełniania zmiennej według stanu. Jeśli wyciągasz listę dla każdego stanu pojedynczo, istnieje lepsze rozwiązanie w jednym ujęciu:

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];

Wyniki:

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

Aby zamówić według nazwy miasta w każdym stanie:

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];

W usłudze Azure SQL Database lub SQL Server 2017+ możesz użyć nowej STRING_AGG()funkcji :

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

I uporządkowane według nazwy miasta:

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

— Aaron Bertrand
źródło

Dzięki Aaron. Moje obecne rozwiązanie jest prawie identyczne z twoim, z wyjątkiem tego, że używam DISTINCT zamiast GROUP BY.

— user2732180,

2

@ user2732180 Należy użyć GROUP BY, ponieważ jest bardziej prawdopodobne, że konkatenacja zostanie przeprowadzona raz dla każdego stanu. Dzięki DISTINCT na przykład zastosuje tę samą konkatenację dla każdego wystąpienia Kalifornii, a dopiero potem odrzuci całą pracę, jaką wykonał, tworząc te duplikaty.

— Aaron Bertrand

6

Aby dodać do powyższej odpowiedzi Aarona ...

Pamiętaj, że ORDER BYmoże dojść do złamania, włączając tylko ostatni element do zapytania. W moim przypadku nie grupowałem, więc nie jestem pewien, czy to robi różnicę. Używam SQL 2014. W moim przypadku mam coś takiego jak wartość1, wartość2, wartość3 ... ale mój wynik w zmiennej to tylko wartość3.

Aaron skomentował:

Zostało to zgłoszone co najmniej cztery razy w Connect:

Przykładowa odpowiedź Microsoft:

Zachowanie, które widzisz, jest zgodne z projektem. Używanie operacji przypisania (konkatenacja w tym przykładzie) w zapytaniach z klauzulą ORDER BY ma niezdefiniowane zachowanie.

Odpowiedź odwołuje się również do KB 287515:

PRB: Plan wykonania i wyniki zagregowanych zapytań konkatenacyjnych zależą od lokalizacji wyrażenia

Rozwiązaniem jest użycie FOR XML PATH(drugie podejście w odpowiedzi Aarona), jeśli kolejność konkatenacji jest ważna i, oczywiście, jeśli chcesz mieć pewność, że uwzględnisz wszystkie wartości. Zobacz także:

nvarchar concatenation / index / nvarchar (max) niewytłumaczalne zachowanie w przypadku przepełnienia stosu

— ebol2000
źródło