Как создать функцию SQL Server для «объединения» нескольких строк из подзапроса в одно поле с разделителями?

Для иллюстрации предположим, что у меня 9X_sqlselect есть две следующие таблицы:

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 

Я хочу написать 9X_sql-query запрос, чтобы вернуть следующие результаты:

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

Я 9X_sql-select знаю, что это можно сделать с помощью серверных 9X_sql курсоров, т.е.:

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 

Однако, как видите, это требует 9X_string-concatenation большого количества кода. Мне бы хотелось 9X_sql-syntax получить общую функцию, которая позволила 9X_sql-query бы мне сделать что-то вроде этого:

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

Возможно 9X_string-concatenation ли это? Или что-то подобное?

207
1

  • Аналогичный ответ с более полным ...
13
Общее количество ответов: 13

Ответ #1

Ответ на вопрос: Как создать функцию SQL Server для «объединения» нескольких строк из подзапроса в одно поле с разделителями?

Если вы используете SQL Server 2005, вы 9X_sql-server можете использовать команду 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] 

Это 9X_sqlselect намного проще, чем использование курсора, и 9X_mssql работает достаточно хорошо.

275
4

  • Это будет хорошо работать с этими данными, но если ваши данные могут содержать специальные символы xml (например, <,>, &), они ...

Ответ #2

Ответ на вопрос: Как создать функцию SQL Server для «объединения» нескольких строк из подзапроса в одно поле с разделителями?

Обратите внимание, что Matt's code приведет к появлению 9X_select-statement дополнительной запятой в конце строки; использование 9X_select-statement COALESCE (или ISNULL в этом отношении), как 9X_sql-syntax показано в ссылке в сообщении Ланса, использует 9X_sql-statement аналогичный метод, но не оставляет вам лишнюю 9X_sql-srever запятую для удаления. Для полноты картины 9X_mssql вот соответствующий код из ссылки Лэнса 9X_sql-srever на sqlteam.com:

DECLARE @EmployeeList varchar(100) SELECT @EmployeeList = COALESCE(@EmployeeList + ', ', '') + CAST(EmpUniqueID AS varchar(5)) FROM SalesCallsEmployees WHERE SalCal_UniqueID = 1 

90
5

  • Он может пропустить данные из результата, ...

Ответ #3

Ответ на вопрос: Как создать функцию SQL Server для «объединения» нескольких строк из подзапроса в одно поле с разделителями?

Я не верю, что это можно сделать в одном 9X_ms-sql-server запросе, но вы можете проделывать подобные 9X_sql-syntax трюки с временной переменной:

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

Это определенно 9X_sql-syntax меньше кода, чем обход курсора, и, вероятно, более 9X_sql-query эффективно.

49
4

  • Я почти уверен, что вы можете в ...

Ответ #4

Ответ на вопрос: Как создать функцию SQL Server для «объединения» нескольких строк из подзапроса в одно поле с разделителями?

В одном запросе SQL без использования предложения 9X_sqlselect FOR XML.
Общее табличное выражение используется 9X_sql-statement для рекурсивного объединения результатов.

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

26
4

  • @PeonProgrammer нет, он работает очень плохо для больших наборов результатов и может выдать вам ...

Ответ #5

Ответ на вопрос: Как создать функцию SQL Server для «объединения» нескольких строк из подзапроса в одно поле с разделителями?

Из того, что я вижу, FOR XML (как было опубликовано 9X_sql ранее) - это единственный способ сделать 9X_sql-select это, если вы хотите также выбрать другие 9X_sql-statement столбцы (что, как я полагаю, большинство), как 9X_sql-statement это делает OP. Использование COALESCE(@var... не позволяет 9X_mssql включать другие столбцы.

Обновление: Благодаря 9X_sql-srever programmingsolutions.net есть способ удалить "завершающую" запятую 9X_ms-sql-server до. Превратив его в начальную запятую и 9X_sql-statement используя функцию MSSQL STUFF, вы можете заменить 9X_sql-query первый символ (начальную запятую) пустой 9X_select-statement строкой, как показано ниже:

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

24
0

Ответ #6

Ответ на вопрос: Как создать функцию SQL Server для «объединения» нескольких строк из подзапроса в одно поле с разделителями?

В SQL Server 2005

SELECT Stuff( (SELECT N', ' + Name FROM Names FOR XML PATH(''),TYPE) .value('text()[1]','nvarchar(max)'),1,2,N'') 

В SQL Server 2016

вы можете использовать FOR JSON syntax

т.е.

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 

И результат станет

Id Emails 1 [email protected] 2 NULL 3 [email protected], [email protected] 

Это 9X_sql-query будет работать, даже если ваши данные содержат 9X_string-concatenation недопустимые символы XML

'"}, {" ":"' безопасно, потому что если ваши данные содержат '"}, {" ":"', они 9X_sql-syntax будут заменены на "}, {\" _ \ ": \"

Вы 9X_sql-statement можете заменить "," любым разделителем 9X_sql-srever строк


А в SQL Server 2017 - База данных SQL Azure

Вы можете использовать новый STRING_AGG function

22
4

  • Привет, Баодад, результаты такие же, но, поскольку я тестировал, производительность лучше при ...

Ответ #7

Ответ на вопрос: Как создать функцию SQL Server для «объединения» нескольких строк из подзапроса в одно поле с разделителями?

Приведенный ниже код будет работать для 9X_select-statement 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 

14
1

  • Этот VARCHAR (1000), это какой-то предел, не так ли? Потому что, когда я запускаю аналогичный запрос конкатенац ...

Ответ #8

Ответ на вопрос: Как создать функцию SQL Server для «объединения» нескольких строк из подзапроса в одно поле с разделителями?

Я нашел решение, создав следующую функцию:

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 

Использование:

SELECT dbo.JoinTexts(' , ', 'Y') 

9X_sql-srever

7
2

  • Это очень похоже на [Майка Пауэлла] (http://stackoverflow.com/questions/6899/is-there-a-way-to-create-a-sql-server-function-to-join-multiple-rows- from-a-sub / 7192 # 7192) и ...

Ответ #9

Ответ на вопрос: Как создать функцию SQL Server для «объединения» нескольких строк из подзапроса в одно поле с разделителями?

Ответ Мана не сработал для меня, поэтому 9X_sql-statement я внес некоторые изменения в этот ответ, чтобы 9X_sql-statement заставить его работать. Надеюсь, это кому-то 9X_sql-select поможет. Использование 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] 

4
0

Ответ #10

Ответ на вопрос: Как создать функцию SQL Server для «объединения» нескольких строк из подзапроса в одно поле с разделителями?

ПРИМЕЧАНИЕ К ВЕРСИИ. Вы должны использовать SQL Server 2005 или выше с уровнем совместимости 90 или выше для этого решения.

См. этот MSDN article для первого примера создания определяемой 9X_sqlselect пользователем агрегатной функции, которая 9X_select-statement объединяет набор строковых значений, взятых 9X_sql-select из столбца в таблице.

Моя скромная рекомендация 9X_select-statement - опустить добавленную запятую, чтобы вы 9X_sql могли использовать свой собственный специальный 9X_mssql разделитель, если таковой имеется.

Ссылаясь 9X_string-concatenation на версию примера 1 для C#:

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

И

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

Таким образом, когда 9X_sql-srever вы используете свой собственный агрегат, вы 9X_sql-server можете выбрать использование собственного 9X_sql-select разделителя или его отсутствие, например:

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

ПРИМЕЧАНИЕ. Будьте 9X_sql-statement осторожны с объемом данных, которые вы пытаетесь 9X_sql-select обработать в совокупности. Если вы попытаетесь 9X_sql-syntax объединить тысячи строк или много очень 9X_sql больших типов данных, вы можете получить 9X_sql-statement ошибку .NET Framework с сообщением «[t] буфера 9X_ms-sql-server недостаточно».

3
0

Ответ #11

Ответ на вопрос: Как создать функцию SQL Server для «объединения» нескольких строк из подзапроса в одно поле с разделителями?

Что касается других ответов, человек, читающий 9X_sql-server ответ, должен знать таблицу транспортных 9X_sql-srever средств и создать таблицу транспортных средств 9X_ms-sql-server и данные для проверки решения.

Ниже приведен 9X_ms-sql-server пример, в котором используется таблица SQL 9X_sqlselect Server «Information_Schema.Columns». При 9X_sql-syntax использовании этого решения не нужно создавать 9X_ms-sql-server таблицы или добавлять данные. В этом примере 9X_ms-sql-server создается список имен столбцов, разделенных 9X_ms-sql-server запятыми, для всех таблиц в базе данных.

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 

3
0

Ответ #12

Ответ на вопрос: Как создать функцию SQL Server для «объединения» нескольких строк из подзапроса в одно поле с разделителями?

Если вы используете SQL Server 2005, вы 9X_string-concatenation можете написать custom CLR aggregate function, чтобы справиться с этим.

Версия 9X_mssql 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()); } } 

1
0

Ответ #13

Ответ на вопрос: Как создать функцию SQL Server для «объединения» нескольких строк из подзапроса в одно поле с разделителями?

Попробуйте этот запрос

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 

9X_select-statement

1
0