Concatenate rows (sql server 2000)
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...
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."
精彩评论