Concatenate multiple rows into single string

The problem was the following.
I’m creating a report in SSRS (Microsoft SQL Server Reporting Services). In this report I need to display a table with assets. These assets have an N-N relationship with another table, through a link-table. This creates a 3 dimensional table, but I can only display 2 dimensions on paper of course.
As a result the 3rd dimension has to be reduces to a column of the assets table.

Here is the resulting query:

SELECT DISTINCT assets.assetid, assets.name, REPLACE(temp.CProcedures, ',', Char(10)+Char(13))
FROM Filtered_asset AS assets
LEFT OUTER JOIN (
SELECT Linker.assetid, Left(Linker.CProcedures, Len(Linker.CProcedures)-1) AS CProcedures
FROM (
SELECT distinct ST2.assetid,
(SELECT ST1.name + ',' AS [text()]
FROM (
SELECT Filtered_calibrationprocedure.name, Filtered_asset_calibrationprocedure.assetid
FROM Filtered_asset_calibrationprocedure
LEFT OUTER JOIN Filteredcalibrationprocedure ON Filtered_asset_calibrationprocedure.calibrationprocedureid = Filtered_calibrationprocedure.calibrationprocedureid) ST1
WHERE ST1.assetid = ST2.assetid
for XML PATH ('')) [CProcedures]
FROM (
SELECT Filtered_calibrationprocedure.name, Filtered_asset_calibrationprocedure.assetid
FROM Filtered_asset_calibrationprocedure
LEFT OUTER JOIN Filtered_calibrationprocedure ON Filtered_asset_calibrationprocedure.calibrationprocedureid = Filtered_calibrationprocedure.calibrationprocedureid) ST2
) AS Linker) AS temp on temp.assetid = assets.assetid

No Responses to “Concatenate multiple rows into single string”.

Leave a response