Denormalizing One-To-Many relationships with for xml path('')
In a number of cases - among others Data Warehouse Dimensions - you want to have the "Many" relations on one row.
12/12/20231 min read
FOR XML PATH('')
SELECT T1.EmployeeID, Nr = COUNT(*)
FROM [HumanResources].[EmployeeDepartmentHistory] T1
GROUP BY T1.EmployeeID HAVING COUNT(*) > 1
)
SELECT
T1.EmployeeID
, DepartmentList =
(select stuff(
(select ', ' + cast(t999a.DepartmentID as varchar(7)) + ' - ' + t999b.Name
from [HumanResources].[EmployeeDepartmentHistory] t999a
inner join [HumanResources].[Department] t999b on t999b.DepartmentID=t999a.DepartmentID
where t999a.EmployeeID=T1.EmployeeID
order by t999a.DepartmentID
for xml path('')), 1, 2, '') as FieldList
)
FROM EmployeeDepartmentCTE T1
ORDER BY T1.EmployeeID
Full content of a table on one row
DECLARE @vcString varchar(max) = ''
SELECT @vcString = @vcString + Cast(DP.DepartmentID as varchar(10)) + ' - ' + DP.Name + '; '
FROM HumanResources.Department DP
SELECT @vcString