Как создать функцию 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 ли это? Или что-то подобное?
- Аналогичный ответ с более полным ...
Ответ #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 работает достаточно хорошо.
- Это будет хорошо работать с этими данными, но если ваши данные могут содержать специальные символы 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
- Он может пропустить данные из результата, ...
Ответ #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 эффективно.
- Я почти уверен, что вы можете в ...
Ответ #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
- @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
Ответ #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
- Привет, Баодад, результаты такие же, но, поскольку я тестировал, производительность лучше при ...
Ответ #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
- Этот 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
- Это очень похоже на [Майка Пауэлла] (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]
Ответ #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 недостаточно».
Ответ #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
Ответ #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()); } }
Ответ #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
-
44
-
19
-
2
-
4
-
3
-
1
-
8
-
5
-
5
-
7
-
2
-
1
-
1
-
1
-
2
-
1
-
2
-
16
-
16
-
14
-
27
-
3
-
3
-
12
-
15
-
11
-
2
-
7
-
5
-
9
-
17
-
4
-
4
-
17
-
6
-
28
-
5
-
3
-
6
-
9
-
5
-
7
-
3
-
3
-
3
-
12
-
7
-
5
-
10
-
4