Группировка данных массива в одну строку.
Для 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, который не всегда совместим с другими аналитическими и информационными системами