I wanted to post some samples how you can accomplish column result concatenation into one string with sql server 2005.
If you know another way, let me know.
The following results are from the Categories table of the Northwind database.
Sample 1:
DECLARE @categories AS VARCHAR(1024)
SELECT @categories = CASE IsNull(@categories,'')
WHEN '' THEN CategoryName
ELSE @categories + '; ' + CategoryName
END
FROM Categories
ORDER BY CategoryName
SELECT @categories
DECLARE @categories AS VARCHAR(1024)
SELECT @categories = COALESCE(@categories + '; ', '') + CategoryName
FROM Categories
ORDER BY CategoryName
SELECT @categories AS CategoryNames
DECLARE @categories AS VARCHAR(1024)
SELECT @categories = CAST
(
(SELECT CategoryName + '; ' AS [text()]
FROM Categories
FOR XML PATH(''))
AS VARCHAR(1024))
SELECT @categories
DECLARE @categories AS VARCHAR(1024)
SELECT @categories = CAST
(
(SELECT CategoryName AS CAT FROM Categories FOR XML RAW, ELEMENTS, TYPE).query
(
'for $CAT in (row/CAT) order by $CAT ascending return concat($CAT, "; ")'
)AS VARCHAR(1024)
)
SELECT @categories