Concatenate Column results into a single string in SQL Server 2005

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.

image

image


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


Sample 2: 
DECLARE @categories AS VARCHAR(1024)
SELECT @categories = COALESCE(@categories + '; ', '') + CategoryName  
FROM Categories
ORDER BY CategoryName
SELECT @categories AS CategoryNames


Sample 3:
DECLARE @categories AS VARCHAR(1024)
SELECT @categories = CAST
(
    (SELECT  CategoryName + '; ' AS [text()]
    FROM Categories
    FOR XML PATH(''))
AS VARCHAR(1024))
SELECT @categories


Sample 4:
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
Posted on November 29, 2007 22:48 by blogmaster
Tags:
Categories: Sql Server
Actions: E-mail | Permalink | Comments (122) | Trackback