Jak utworzyć funkcję SQL Server, aby "połączyć" wiele wierszy z zapytania podrzędnego w jedno rozdzielone pole? [duplikat]
Aby zilustrować, Załóżmy, że mam dwie tabele w następujący sposób:
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
Chcę napisać zapytanie, aby zwrócić następujące wyniki:
VehicleID Name Locations
1 Chuck New York, Seattle, Vancouver
2 Larry Los Angeles, Houston
Wiem, że można to zrobić za pomocą kursorów po stronie serwera, czyli:
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
Jednakże, jak widzisz, wymaga to dużej ilości kodu. To, co chciałbym, to ogólna funkcja, która pozwoliłaby mi zrobić coś takiego to:
SELECT VehicleID
, Name
, JOIN(SELECT City FROM Locations WHERE VehicleID = Vehicles.VehicleID, ', ') AS Locations
FROM Vehicles
Czy to możliwe? Albo coś podobnego? 13 answers
Jeśli używasz SQL Server 2005, możesz użyć polecenia 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]
Jest to o wiele łatwiejsze niż używanie kursora i wydaje się działać dość dobrze.
Warning: date(): Invalid date.timezone value 'Europe/Kyiv', we selected the timezone 'UTC' for now. in /var/www/agent_stack/data/www/doraprojects.net/template/agent.layouts/content.php on line 54
2014-10-30 00:55:26
Zauważ, że Kod Matta spowoduje dodanie dodatkowego przecinka na końcu łańcucha; użycie COALESCE (lub ISNULL), jak pokazano w linku w poście Lance ' a, używa podobnej metody, ale nie pozostawia dodatkowego przecinka do usunięcia. Dla kompletności, oto odpowiedni kod z linku Lance ' a na sqlteam.com:
DECLARE @EmployeeList varchar(100)
SELECT @EmployeeList = COALESCE(@EmployeeList + ', ', '') +
CAST(EmpUniqueID AS varchar(5))
FROM SalesCallsEmployees
WHERE SalCal_UniqueID = 1
Warning: date(): Invalid date.timezone value 'Europe/Kyiv', we selected the timezone 'UTC' for now. in /var/www/agent_stack/data/www/doraprojects.net/template/agent.layouts/content.php on line 54
2017-05-23 10:31:29
Nie wierzę, że jest sposób, aby to zrobić w ramach jednego zapytania, ale można grać takie sztuczki z tymczasową zmienną:
declare @s varchar(max)
set @s = ''
select @s = @s + City + ',' from Locations
select @s
To zdecydowanie mniej kodu niż chodzenie po kursorze i prawdopodobnie bardziej wydajne.
Warning: date(): Invalid date.timezone value 'Europe/Kyiv', we selected the timezone 'UTC' for now. in /var/www/agent_stack/data/www/doraprojects.net/template/agent.layouts/content.php on line 54
2008-08-10 00:12:25
W pojedynczym zapytaniu SQL, bez użycia klauzuli FOR XML.
Wspólne wyrażenie tabeli jest używane do rekurencyjnego łączenia wyników.
-- 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
Warning: date(): Invalid date.timezone value 'Europe/Kyiv', we selected the timezone 'UTC' for now. in /var/www/agent_stack/data/www/doraprojects.net/template/agent.layouts/content.php on line 54
2011-01-06 15:20:41
Z tego, co widzę FOR XML
(Jak napisano wcześniej) jest to jedyny sposób, aby to zrobić, jeśli chcesz również wybrać inne kolumny (co chyba większość), jak robi OP.
Użycie COALESCE(@var...
nie pozwala na wstawianie innych kolumn.
Update:
Dzięki programmingsolutions.net istnieje sposób, aby usunąć "końcowy" przecinek do.
Zmieniając go w przecinek wiodący i używając funkcji STUFF
MSSQL, możesz zastąpić pierwszy znak (przecinek wiodący) pustym łańcuchem jako poniżej:
stuff(
(select ',' + Column
from Table
inner where inner.Id = outer.Id
for xml path('')
), 1,1,'') as Values
Warning: date(): Invalid date.timezone value 'Europe/Kyiv', we selected the timezone 'UTC' for now. in /var/www/agent_stack/data/www/doraprojects.net/template/agent.layouts/content.php on line 54
2011-02-09 06:03:20
In SQL Server 2005
SELECT Stuff(
(SELECT N', ' + Name FROM Names FOR XML PATH(''),TYPE)
.value('text()[1]','nvarchar(max)'),1,2,N'')
W SQL Server 2016
Możesz użyć dla składni JSON
Tzn.
SELECT per.ID,
Emails = JSON_VALUE(
REPLACE(
(SELECT _ = em.Email FROM Email em WHERE em.Person = per.ID FOR JSON PATH)
,'"},{"_":"',', '),'$[0]._'
)
FROM Person per
I wynik będzie
Id Emails
1 [email protected]
2 NULL
3 [email protected], [email protected]
To zadziała nawet Twoje dane zawierają nieprawidłowe znaki XML
The '"},{"":"' jest bezpieczny, ponieważ jeśli dane zawierają '"},{"":"', będzie uciekał do "},{\"_\":\"
Możesz zastąpić', ' dowolnym separatorem łańcuchów
I w SQL Server 2017, Azure SQL Database
Możesz użyć nowej funkcji STRING_AGG
Warning: date(): Invalid date.timezone value 'Europe/Kyiv', we selected the timezone 'UTC' for now. in /var/www/agent_stack/data/www/doraprojects.net/template/agent.layouts/content.php on line 54
2018-06-01 01:39:50
Poniższy kod będzie działał dla Sql Server 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
Warning: date(): Invalid date.timezone value 'Europe/Kyiv', we selected the timezone 'UTC' for now. in /var/www/agent_stack/data/www/doraprojects.net/template/agent.layouts/content.php on line 54
2009-06-18 12:41:27
Znalazłem rozwiązanie tworząc następującą funkcję:
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
Użycie:
SELECT dbo.JoinTexts(' , ', 'Y')
Warning: date(): Invalid date.timezone value 'Europe/Kyiv', we selected the timezone 'UTC' for now. in /var/www/agent_stack/data/www/doraprojects.net/template/agent.layouts/content.php on line 54
2011-05-30 15:15:06
ODPOWIEDŹ Mun nie działa dla mnie, więc zrobiłem kilka zmian w tej odpowiedzi, aby ją uruchomić. Mam nadzieję, że to komuś pomoże. Korzystanie z 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]
Warning: date(): Invalid date.timezone value 'Europe/Kyiv', we selected the timezone 'UTC' for now. in /var/www/agent_stack/data/www/doraprojects.net/template/agent.layouts/content.php on line 54
2016-10-13 00:07:03
wersja uwaga: musisz używać SQL Server 2005 lub nowszego z poziomem zgodności ustawionym na 90 lub nowszym dla tego rozwiązania.
Zobacz ten artykuł MSDN dla pierwszego przykładu tworzenia funkcji agregującej zdefiniowanej przez użytkownika, która łączy zbiór wartości łańcuchowych pobranych z kolumny w tabeli.
Moim skromnym zaleceniem byłoby pominięcie załączonego przecinka, abyś mógł użyć własnego ogranicznika ad-hoc, jeśli taki istnieje.
Odwołując się do wersji C# Przykład 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();
W ten sposób, gdy używasz niestandardowego agregatu, możesz zdecydować się na użycie własnego ogranicznika lub żadnego w ogóle, na przykład:
SELECT dbo.CONCATENATE(column1 + '|') from table1
Uwaga: uważaj na ilość danych, które próbujesz przetworzyć w swoim agregacie. Jeśli spróbujesz połączyć tysiące wierszy lub wiele bardzo dużych typów danych, może pojawić się błąd.NET Framework stwierdzający "[t]bufor jest niewystarczający."
Warning: date(): Invalid date.timezone value 'Europe/Kyiv', we selected the timezone 'UTC' for now. in /var/www/agent_stack/data/www/doraprojects.net/template/agent.layouts/content.php on line 54
2010-02-10 00:28:17
W przypadku pozostałych odpowiedzi osoba czytająca odpowiedź musi znać tabelę pojazdu i utworzyć tabelę pojazdu oraz dane, aby przetestować rozwiązanie.
Poniżej znajduje się przykład, który używa SQL Server "Information_Schema.Kolumny " tabela. Dzięki temu rozwiązaniu nie trzeba tworzyć tabel ani dodawać danych. Ten przykład tworzy oddzieloną przecinkami listę nazw kolumn dla wszystkich tabel w bazie danych.
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
Warning: date(): Invalid date.timezone value 'Europe/Kyiv', we selected the timezone 'UTC' for now. in /var/www/agent_stack/data/www/doraprojects.net/template/agent.layouts/content.php on line 54
2016-05-04 19:15:00
Spróbuj tego zapytania
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('')
),
'</c><c>',', '
),
'<c>',''
),
'</c>', ''
) AS LocationList
FROM Locations l
) ll ON ll.VehicleId = v.VehicleId
Warning: date(): Invalid date.timezone value 'Europe/Kyiv', we selected the timezone 'UTC' for now. in /var/www/agent_stack/data/www/doraprojects.net/template/agent.layouts/content.php on line 54
2015-09-08 01:51:38
Jeśli używasz SQL Server 2005, możesz napisać custom CLR aggregate function do obsługi tego.
Wersja 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());
}
}
Warning: date(): Invalid date.timezone value 'Europe/Kyiv', we selected the timezone 'UTC' for now. in /var/www/agent_stack/data/www/doraprojects.net/template/agent.layouts/content.php on line 54
2018-04-30 09:12:18