T-SQL | Оператор STUFF |
Группировка данных массива в одну строку.
Для MS SQL Server 2005 и выше.
Например, есть запрос по сотрудникам компании и отделам (на примере БД AdventureWorks):
USE AdventureWorks
SELECT h.EmployeeID AS [Employee ID], d.Name AS [Department Name] FROM HumanResources.EmployeeDepartmentHistory h INNER JOIN HumanResources.Department d ON d.DepartmentID = h.DepartmentID WHERE h.EmployeeID in (96, 140, 274)
Нужно разработать запрос, в котором отделы по каждому сотруднику перечислялись бы в одной строке.
Например, для сотрудника [Employee ID] 274 запрос возвратил бы строку: 'Marketing | Quality Assurance | Purchasing'.
Подобный запрос можно реализовать с помощью оператора STUFF:
--ШАБЛОН --Крутим данные запроса: SELECT ID, [Name] FROM TableName --[Name] - что крутим --ID - по чему крутим --' | ' - символ между значениями в скрученной строке
/* select distinct t1.ID, STUFF((SELECT distinct ' | ' + t2.[Name] from (SELECT ID, [Name] FROM TableName ) t2 where t1.ID = t2.ID FOR XML PATH(''), TYPE ).value('.', 'NVARCHAR(MAX)') ,1,0,'') data from (SELECT ID, [Name] FROM TableName ) t1 */
--ПРИМЕР (запрос по сотрудникам и отделам, в которых они работали; БД AdventureWorks MS SQL Server 2005) --Крутим данные запроса: -- SELECT -- h.EmployeeID AS [Employee ID], -- d.Name AS [Department Name] -- FROM HumanResources.EmployeeDepartmentHistory h -- INNER JOIN HumanResources.Department d ON d.DepartmentID = h.DepartmentID -- WHERE h.EmployeeID in (96, 140, 274)
--[Department Name] в строку по [Employee ID]
USE AdventureWorks
select distinct t1.[Employee ID], STUFF((SELECT distinct ' | ' + t2.[Department Name] from (SELECT h.EmployeeID AS [Employee ID], d.Name AS [Department Name] FROM HumanResources.EmployeeDepartmentHistory h INNER JOIN HumanResources.Department d ON d.DepartmentID = h.DepartmentID WHERE h.EmployeeID in (96, 140, 274) ) t2 where t1.[Employee ID] = t2.[Employee ID] FOR XML PATH(''), TYPE ).value('.', 'NVARCHAR(MAX)') ,1,0,'') data from (SELECT h.EmployeeID AS [Employee ID], d.Name AS [Department Name] FROM HumanResources.EmployeeDepartmentHistory h INNER JOIN HumanResources.Department d ON d.DepartmentID = h.DepartmentID WHERE h.EmployeeID in (96, 140, 274) ) t1
При желании, полученное поле можно отформатировать (привести к тексту, например VARCHAR(7000)*) и отбросить из строки первый избыточный символ (разделитель элементов массива, в примере это первые 3 символа):
USE AdventureWorks
select [Employee ID], CAST(RIGHT(data, LEN(data) - 3) AS VARCHAR(7000)) AS Department from (
select distinct t1.[Employee ID], STUFF((SELECT distinct ' | ' + t2.[Department Name] from (SELECT h.EmployeeID AS [Employee ID], d.Name AS [Department Name] FROM HumanResources.EmployeeDepartmentHistory h INNER JOIN HumanResources.Department d ON d.DepartmentID = h.DepartmentID WHERE h.EmployeeID in (96, 140, 274) ) t2 where t1.[Employee ID] = t2.[Employee ID] FOR XML PATH(''), TYPE ).value('.', 'NVARCHAR(MAX)') ,1,0,'') data from (SELECT h.EmployeeID AS [Employee ID], d.Name AS [Department Name] FROM HumanResources.EmployeeDepartmentHistory h INNER JOIN HumanResources.Department d ON d.DepartmentID = h.DepartmentID WHERE h.EmployeeID in (96, 140, 274) ) t1
) Employee_Dept
* Результат STUFF-а может быть в формате BLOB, который не всегда совместим с другими аналитическими и информационными системами
|