Cum se creează o funcție SQL Server pentru a "se alătura" mai multor rânduri dintr-o subchetă într-un singur câmp delimitat?

Pentru a ilustra, presupuneți că am două tabele după cum urmează:

VehicleID Name
1         Chuck
2         Larry

LocationID VehicleID City
1          1         New York
2          1         Seattle
3          1         Vancouver
4          2         Los Angeles
5          2         Houston

Vreau să scriu o interogare pentru a returna următoarele rezultate:

VehicleID Name    Locations
1         Chuck   New York, Seattle, Vancouver
2         Larry   Los Angeles, Houston

Știu că acest lucru se poate face folosind cursoare de pe server, și anume:

DECLARE @VehicleID int
DECLARE @VehicleName varchar(100)
DECLARE @LocationCity varchar(100)
DECLARE @Locations varchar(4000)
DECLARE @Results TABLE
(
  VehicleID int
  Name varchar(100)
  Locations varchar(4000)
)

DECLARE VehiclesCursor CURSOR FOR
SELECT
  [VehicleID]
, [Name]
FROM [Vehicles]

OPEN VehiclesCursor

FETCH NEXT FROM VehiclesCursor INTO
  @VehicleID
, @VehicleName
WHILE @@FETCH_STATUS = 0
BEGIN

  SET @Locations = ''

  DECLARE LocationsCursor CURSOR FOR
  SELECT
    [City]
  FROM [Locations]
  WHERE [VehicleID] = @VehicleID

  OPEN LocationsCursor

  FETCH NEXT FROM LocationsCursor INTO
    @LocationCity
  WHILE @@FETCH_STATUS = 0
  BEGIN
    SET @Locations = @Locations + @LocationCity

    FETCH NEXT FROM LocationsCursor INTO
      @LocationCity
  END
  CLOSE LocationsCursor
  DEALLOCATE LocationsCursor

  INSERT INTO @Results (VehicleID, Name, Locations) SELECT @VehicleID, @Name, @Locations

END     
CLOSE VehiclesCursor
DEALLOCATE VehiclesCursor

SELECT * FROM @Results

Cu toate acestea, după cum puteți vedea, acest lucru necesită mult cod. Ceea ce aș vrea este o funcție generică care să-mi permită să fac așa ceva:

SELECT VehicleID
     , Name
     , JOIN(SELECT City FROM Locations WHERE VehicleID = Vehicles.VehicleID, ', ') AS Locations
FROM Vehicles

Este posibil? Sau ceva similar?

0
fr hi bn
Un răspuns similar cu un răspuns mai complet stackoverflow.com/a/17591536/1587302
adăugat autor Narkha, sursa

13 răspunsuri

Rețineți că Codul lui Matt va duce la o virgulă suplimentară la sfârșitul șirului; folosind COALESCE (sau ISNULL pentru asta), după cum se arată în link-ul din postarea lui Lance, folosește o metodă similară, dar nu vă lasă cu o virgulă suplimentară pentru ao elimina. Din motive de exhaustivitate, iată codul relevant de la linkul Lance pe sqlteam.com:

DECLARE @EmployeeList varchar(100)
SELECT @EmployeeList = COALESCE(@EmployeeList + ', ', '') + 
    CAST(EmpUniqueID AS varchar(5))
FROM SalesCallsEmployees
WHERE SalCal_UniqueID = 1
0
adăugat
Aceasta nu este o soluție sigură .
adăugat autor Lukáš Lánský, sursa
Nicio virgulă extra, care este drăguță, dar mult mai ușor de citit și de înțeles, în opinia mea, decât soluția acceptată. Mulţumesc mult!
adăugat autor Beska, sursa
Adăugați ORDER BY pentru o comandă sigură?
adăugat autor Pete Alvin, sursa
@lukasLansky sale fiabile atâta timp cât nu vă pasă de comandă
adăugat autor codeulike, sursa
S-ar putea sări peste date de la rezultat, chiar dacă nu vă pasă de comandă.
adăugat autor Der_Meister, sursa

Dacă executați SQL Server 2005, puteți scrie un funcția CLR personalizată agregat pentru a gestiona acest lucru.

Versiunea C #:

using System;
using System.Data;
using System.Data.SqlClient;
using System.Data.SqlTypes;
using System.Text;
using Microsoft.SqlServer.Server;
[Serializable]
[Microsoft.SqlServer.Server.SqlUserDefinedAggregate(Format.UserDefined,MaxByteSize=8000)]
public class CSV:IBinarySerialize
{
    private StringBuilder Result;
    public void Init() {
        this.Result = new StringBuilder();
    }

    public void Accumulate(SqlString Value) {
        if (Value.IsNull) return;
        this.Result.Append(Value.Value).Append(",");
    }
    public void Merge(CSV Group) {
        this.Result.Append(Group.Result);
    }
    public SqlString Terminate() {
        return new SqlString(this.Result.ToString());
    }
    public void Read(System.IO.BinaryReader r) {
        this.Result = new StringBuilder(r.ReadString());
    }
    public void Write(System.IO.BinaryWriter w) {
        w.Write(this.Result.ToString());
    }
}
0
adăugat

Nu cred că există o modalitate de ao face într-o singură interogare, dar puteți juca astfel de trucuri cu o variabilă temporară:

declare @s varchar(max)
set @s = ''
select @s = @s + City + ',' from Locations

select @s

Este cu siguranță un cod mai mic decât mersul pe un cursor și, probabil, mai eficient.

0
adăugat
"Nu cred că există o modalitate de ao face într-o singură interogare" Da, există. SQL Server 2005 a avut ambele FOR xml și CTEs.
adăugat autor T.J. Crowder, sursa
Sunt destul de sigur că puteți lua "probabil" ultima linie.
adăugat autor Marc Gravell, sursa
Nu este de încredere, depinde de planul de execuție, rândurile ar putea fi pierdute. Vedeți KB.
adăugat autor Der_Meister, sursa
Care este această tehnică sau caracteristică numită? Când se face o alocare SELECT @s = @s a variabilei care include valoarea sa existentă și se face din nou pentru fiecare rând din setul de rezultate?
adăugat autor Baodad, sursa

VERSIUNE NOTĂ: Trebuie să utilizați SQL Server 2005 sau o versiune ulterioară cu nivel de compatibilitate setat la 90 sau mai mare pentru această soluție.

Vedeți acest Articol MSDN pentru primul exemplu de creare a unei funcții agregate definite de utilizator care concateniază un set de valori șir luate dintr-o coloană într-un tabel.

RecomȘiarea mea umilă ar fi să las în afară virgula atașată, ca să poți folosi propriul delimiter ad-hoc, dacă există.

Referindu-se la versiunea C# a exemplului 1:

change:  this.intermediateResult.Append(value.Value).Append(',');
    to:  this.intermediateResult.Append(value.Value);

Și

change:  output = this.intermediateResult.ToString(0, this.intermediateResult.Length - 1);
    to:  output = this.intermediateResult.ToString();

În acest fel, atunci când utilizați agregatul personalizat, puteți opta să utilizați delimitatorul propriu sau deloc, cum ar fi:

SELECT dbo.CONCATENATE(column1 + '|') from table1

NOTE: Be careful about the amount of the data you attempt to process in your aggregate. If you try to concatenate thousȘis of rows or many very large datatypes you may get a .NET Framework error stating "[t]he buffer is insufficient."

0
adăugat

Cu celelalte răspunsuri, persoana care citește răspunsul trebuie să fie conștientă de tabelul vehiculului și să creeze masa și datele vehiculului pentru a testa o soluție.

Mai jos este un exemplu care utilizează tabelul SQL Server "Information_Schema.Columns". Prin utilizarea acestei soluții, nu trebuie create tabele sau adăugate date. Acest exemplu creează o listă separată de virgule de nume de coloane pentru toate tabelele din baza de date.

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 
0
adăugat

Codul de mai jos va funcționa pentru serverul SQL 2000/2005/2008

CREATE FUNCTION fnConcatVehicleCities(@VehicleId SMALLINT)
RETURNS VARCHAR(1000) AS
BEGIN
  DECLARE @csvCities VARCHAR(1000)
  SELECT @csvCities = COALESCE(@csvCities + ', ', '') + COALESCE(City,'')
  FROM Vehicles 
  WHERE VehicleId = @VehicleId 
  return @csvCities
END

-- //Once the User defined function is created then run the below sql

SELECT VehicleID
     , dbo.fnConcatVehicleCities(VehicleId) AS Locations
FROM Vehicles
GROUP BY VehicleID
0
adăugat
ai încercat Varchar (max)?
adăugat autor Binoj Antony, sursa
Acest VARCHAR (1000), asta eo limită, nu-i așa? Când executați o interogare de concatenare similară pe o listă de coloane, se va opri doar în jurul a aproximativ 950 de caractere, indiferent de mărimea specificată.
adăugat autor John Leidegren, sursa

Dacă utilizați SQL Server 2005, puteți utiliza comanda FOR xml PATH.

SELECT [VehicleID]
     , [Name]
     , (STUFF((SELECT CAST(', ' + [City] AS VARCHAR(MAX)) 
         FROM [Location] 
         WHERE (VehicleID = Vehicle.VehicleID) 
         FOR xml PATH ('')), 1, 2, '')) AS Locations
FROM [Vehicle]

Este mult mai ușor decât utilizarea unui cursor și pare să funcționeze destul de bine.

0
adăugat
ați avea o idee despre cum să inserați o linie întreruptă mai degrabă decât ',' ? mulțumesc, James
adăugat autor James Parish, sursa
am devenit putin confuz in timp ce modificam acest cod, asa ca am postat acum propria mea întrebare
adăugat autor James Parish, sursa
@Yames Puteți utiliza un CTE pentru a realiza acest lucru: CU MyCTE (VehicleId, Nume, Locații) AS (SELECT [VehicleID], [Nume], SELECT CAST (Oraș + WHERE (VehicleID = Vehicle.VehicleID) PENTRU xml PATH ('')) AS Locații FROM [Vehicul]) SELECT VehicleId, Name, REPLACE
adăugat autor Mun, sursa
Acest lucru va funcționa bine cu aceste date, dar dacă datele dvs. ar putea avea caractere speciale xml (de exemplu, <,>, &), acestea vor fi înlocuite (<, etc.)
adăugat autor GilM, sursa
Puteți împacheta subcotarea în funcția STUFF pentru a scăpa de virgulă. Doar duceți interogarea cu ',' și apoi împachetați subcotarea în: STUFF ( subquery , 1,2, '')
adăugat autor MickJuice, sursa
Caracterele de rulare pot fi inserate astfel: "un text" + CHAR (13) + CHAR (10) + "text pe următoarea linie".
adăugat autor thefellow3j, sursa

În SQL Server 2005+:

SELECT [VehicleID]
    , [Name]
    , [Locations] = Isnull( Stuff(
        ( SELECT N', ' + [City] FROM [Locations]
        WHERE VehicleID = a.VehicleID
        FOR xml PATH(''),TYPE ).value('text()[1]', 'nvarchar(max)')
    , 1, 2, N''), N'')
FROM [Vehicle] a
0
adăugat
ar fi trebuit să decodeze xml, dacă [City] avea char asemănător cu & <>, ieșirea va deveni & amp; & Lt; & Gt; , dacă sigur că [Orașul] nu are caracterele speciale, atunci este sigur să îl eliminați. ? Steven Chong
adăugat autor Steven Chong, sursa
Bună Baodad, rezultatele sunt aceleași, dar pe măsură ce am testat performanța este mai bună atunci când folosesc "text() [1]" în loc de ".", Nici o diferență mare tho
adăugat autor Steven Chong, sursa
+1. Acest răspuns este subevaluat. Ar trebui să îl editați pentru a menționa că acesta este unul din răspunsurile care nu vor scăpa de caractere speciale precum & <> etc. De asemenea, rezultatele nu vor fi aceleași dacă vom folosi: .value ('.' 'nvarchar (max)') ?
adăugat autor Baodad, sursa
Am scos această bucată: TYPE) .value ('text() [1]', 'nvarchar (max)') .
adăugat autor Adam Nofsinger, sursa

Din ceea ce văd FOR xml (după cum este postat mai devreme) este singura modalitate de a face acest lucru dacă doriți să selectați și alte coloane (pe care le-aș fi ghicit cel mai mult), așa cum le face OP. Folosind COALESCE (@var ... nu permite includerea altor coloane.

Actualizați: Vă mulțumim pentru programmingsolutions.net există o modalitate de a elimina virgulă "trailing". Făcând-o într-o virgulă de frunte și utilizând funcția STUFF a MSSQL, puteți înlocui primul caracter (virgula care duce) cu un șir gol ca mai jos:

stuff(
    (select ',' + Column 
     from Table
         inner where inner.Id = outer.Id 
     for xml path('')
), 1,1,'') as Values
0
adăugat

In a single SQL query, without using the FOR xml clause.
A Common Table Expression is used to recursively concatenate the results.

-- rank locations by incrementing lexicographical order
WITH RankedLocations AS (
  SELECT
    VehicleID,
    City,
    ROW_NUMBER() OVER (
        PARTITION BY VehicleID 
        ORDER BY City
    ) Rank
  FROM
    Locations
),
-- concatenate locations using a recursive query
-- (Common Table Expression)
Concatenations AS (
  -- for each vehicle, select the first location
  SELECT
    VehicleID,
    CONVERT(nvarchar(MAX), City) Cities,
    Rank
  FROM
    RankedLocations
  WHERE
    Rank = 1

  -- then incrementally concatenate with the next location
  -- this will return intermediate concatenations that will be 
  -- filtered out later on
  UNION ALL

  SELECT
    c.VehicleID,
    (c.Cities + ', ' + l.City) Cities,
    l.Rank
  FROM
    Concatenations c -- this is a recursion!
    INNER JOIN RankedLocations l ON
        l.VehicleID = c.VehicleID 
        AND l.Rank = c.Rank + 1
),
-- rank concatenation results by decrementing length 
-- (rank 1 will always be for the longest concatenation)
RankedConcatenations AS (
  SELECT
    VehicleID,
    Cities,
    ROW_NUMBER() OVER (
        PARTITION BY VehicleID 
        ORDER BY Rank DESC
    ) Rank
  FROM 
    Concatenations
)
-- main query
SELECT
  v.VehicleID,
  v.Name,
  c.Cities
FROM
  Vehicles v
  INNER JOIN RankedConcatenations c ON 
    c.VehicleID = v.VehicleID 
    AND c.Rank = 1
0
adăugat
Mulțumiri! Trebuie să convertesc o serie de fragmente de cod SQL exprimate ca rânduri separate de expresii booleene într-o singură expresie complexă de cod și sunt încântată să încerc metoda.
adăugat autor Paul Chernoch, sursa
Multumesc pentru asta. Aceasta este una dintre puținele soluții la această problemă care nu utilizează variabile, funcții, clauza FOR xml sau cod CLR. Aceasta înseamnă că am reușit să vă adaptați soluția pentru a rezolva TSQL începători provocare 4 - concatenarea valorilor din mai multe rânduri .
adăugat autor Iain Samuel McLean Elder, sursa
@PeonProgrammer nu, funcționează foarte prost pentru seturile de rezultate mari și este posibil să vă dea eroarea, "Recurgerea maximă 100 a fost epuizată înainte de finalizarea declarației." (Puteți rezolva acest lucru prin specificarea OPTION (MAXRECURSION 0) la sfârșit, dar interogarea dvs. ar putea dura doar pentru totdeauna pentru a rula.
adăugat autor Kirk Woll, sursa
Are avantaje de performanță față de celelalte soluții?
adăugat autor PeonProgrammer, sursa

Am găsit o soluție prin crearea următoarei funcții:

CREATE FUNCTION [dbo].[JoinTexts]
(
  @delimiter VARCHAR(20) ,
  @whereClause VARCHAR(1)
)
RETURNS VARCHAR(MAX)
AS 
BEGIN
    DECLARE @Texts VARCHAR(MAX)

    SELECT  @Texts = COALESCE(@Texts + @delimiter, '') + T.Texto
    FROM    SomeTable AS T
    WHERE   T.SomeOtherColumn = @whereClause

    RETURN @Texts
END
GO

utilizare:

SELECT dbo.JoinTexts(' , ', 'Y')
0
adăugat
Acest lucru este foarte asemănător cu Mike Powell și răspunsurile lui Binoj Antony .
adăugat autor Andriy M, sursa
O soluție excelentă, deoarece lizibilitatea a fost superioară celorlalte răspunsuri +1
adăugat autor PeonProgrammer, sursa

Încercați această interogare

SELECT v.VehicleId, v.Name, ll.LocationList
FROM Vehicles v 
LEFT JOIN 
    (SELECT 
     DISTINCT
        VehicleId,
        REPLACE(
            REPLACE(
                REPLACE(
                    (
                        SELECT City as c 
                        FROM Locations x 
                        WHERE x.VehicleID = l.VehicleID FOR xml PATH('')
                    ),    
                    '',', '
                 ),
             '',''
            ),
        '', ''
        ) AS LocationList
    FROM Locations l
) ll ON ll.VehicleId = v.VehicleId
0
adăugat

Răspunsul lui Mun nu a funcționat pentru mine, așa că am făcut niște schimbări în răspunsul respectiv pentru a ajunge la lucru. Sper că acest lucru îi ajută pe cineva. Utilizarea SQL Server 2012:

SELECT [VehicleID]
     , [Name]
     , STUFF((SELECT DISTINCT ',' + CONVERT(VARCHAR,City) 
         FROM [Location] 
         WHERE (VehicleID = Vehicle.VehicleID) 
         FOR xml PATH ('')), 1, 2, '') AS Locations
FROM [Vehicle]
0
adăugat