views:

407

answers:

2

Hi, I was using Microsoft SQL server 2005 and was able to concatenate row values based on the following query:

SELECT e1.EMP_ID,
( SELECT cast(Sector_ID as varchar(10)) + ';'
FROM Employee_Sector_relationship e2
WHERE e2.Emp_ID = e1.Emp_ID
ORDER BY Sector_ID
FOR XML PATH('') ) AS Sectors
FROM Employee_Sector_Relationship e1
GROUP BY Emp_ID

But it doesn't work in Microsoft Server 2000. It gives me an error near the for keyword. Can anyone help me to concatenate the row values in Microsoft Server 2000?

+1  A: 
FOR XML PATH

is not available in sql server 2000.

This article discusses different approached for concatenating row values: Concatenating Row Values in Transact-SQL

Giorgi
actually - it **IS** available in SQL Server 2005 and up - but it's **NOT** available in SQL Server **2000**
marc_s
which article are you talkin about
reggie
Answer edited. Link added.
Giorgi
+2  A: 

This is a technique that should work for you. You can execute this in one batch statement if you wish:

DECLARE @EmployeeList varchar(100)

SELECT @EmployeeList = COALESCE(@EmployeeList + ', ', '') + 
   CAST(Emp_UniqueID AS varchar(5))
FROM SalesCallsEmployees
WHERE SalCal_UniqueID = 1

SELECT @EmployeeList

For more information, see Using COALESCE to Build Comma-Delimited String.

RedFilter
awesome man. thanks
reggie