I was working on SSRS 2008 R2 migration and needed a list of data sources to join to the ReportServer.dbo.Catalog table. Since a single report can have several datasources I needed a way to rollup the datasource Names to a single ItemID. I have used CTE's in the past but wanted to give the Cross Apply a go. Cross Applym, considerably easier than a CTE.
Cross Apply Data Source Name for SSRS:
SELECT DISTINCT
ds.ItemID ,
Name = LEFT(dsl.list, LEN(dsl.list) - 1)
FROM
dbo.DataSource AS ds
CROSS APPLY ( SELECT
ds2.Name + ',' AS [text()]
FROM
dbo.DataSource AS ds2
WHERE
ds.ItemID = ds2.ItemID
ORDER BY
ds2.ItemID
FOR
XML PATH('')
) dsl ( list )
WHERE
ds.ItemID IS NOT NULL
AND LEN(ds.Name) > 0
Ref Link:
