Concatenate rows (sql server 2000)

Question

I have the following problem when using SQL Server 2000

The following table has around 200 rows.

Company / Employee
1005 / A
1005 / B
1005 / C
1010 / X
1010 / Y
1020 / L
1020 / M
etc etc

I wish to create the following (comma separated) output:

Company / Employees
1005 / A, B, C
1010 / X, Y
1020 / L, M
etc etc

I'm having a really hard time with this in SQL Server 2000, while 2005 seems to offer easier solutions to solve this issue! I hope someone has a bright idea to explain how to solve this...

Answer 1

If you look inthese FAQs you will find several ways to perform this task:

http://www.tek-tips.com/faq.cfm?pid=183

Answer 2

See if these help:

Concatenating row values in Transact-SQL

How do I concatenate strings from a column into a single row?

Answer 3

I've seen solutions using UDFs in SQL 2000, but they are scary - they're basically doing a brand-new SELECT for every group that might be executed hundreds or thousands of times. You're actually better off using a cursor:

DECLARE @Temp TABLE
(
    Company int NOT NULL PRIMARY KEY,
    Employees varchar(4000) NOT NULL
)

DECLARE
    @LastCompany int,
    @Company int,
    @Employee varchar(100),
    @Employees varchar(4000)

DECLARE crEmployee CURSOR FAST_FORWARD FOR
    SELECT Company, Employee
    FROM @Tbl
    ORDER BY Company
OPEN crEmployee
FETCH NEXT FROM crEmployee INTO @Company, @Employee
WHILE (@@FETCH_STATUS = 0)
BEGIN
    IF (@LastCompany IS NULL)
        SET @Employees = @Employee
    ELSE IF (@Company = @LastCompany)
        SET @Employees = @Employees + ',' + @Employee
    ELSE BEGIN
        INSERT @Temp (Company, Employees)
        VALUES (@LastCompany, @Employees)

        SET @Employees = @Employee
    END
    SET @LastCompany = @Company
    FETCH NEXT FROM crEmployee INTO @Company, @Employee
END
CLOSE crEmployee
DEALLOCATE crEmployee

IF (@Employees IS NOT NULL)
    INSERT @Temp (Company, Employees)
    VALUES (@LastCompany, @Employees)

SELECT * FROM @Temp

There's a solution using a temporary table and UPDATE similar to that used to compute running totals, but it's a beast to look at and in most cases won't do much better than the cursor for performance.

If anyone really desperately wants to see the UPDATE version, I'll post it, but I suggest trying the cursor version first and seeing if it's "good enough."