SQL Server 2000 equivalent of GROUP_CONCAT function

Question

I tried to use the GROUP_CONCAT function in SQL Server 2000 but it returns an error:

'group_concat' is not a recognized function name"

So I guess there is an other function for group_concat in SQL Server 2000? Can you tell me what it is?

Answer 1

Unfortunately since you are using SQL Server 2000 you cannot use FOR XML PATH to concatenate the values together.

Let's say we have the following sample Data:

CREATE TABLE yourtable ([id] int, [name] varchar(4));

INSERT INTO yourtable ([id], [name])
VALUES (1, 'John'), (1, 'Jim'),
    (2, 'Bob'), (3, 'Jane'), (3, 'Bill'), (4, 'Test'), (4, '');

One way you could generate the list together would be to create a function. A sample function would be:

CREATE FUNCTION dbo.List 
( 
    @id int
) 
RETURNS VARCHAR(8000) 
AS 
BEGIN 
    DECLARE @r VARCHAR(8000) 
      SELECT @r = ISNULL(@r+', ', '') + name
      FROM dbo.yourtable 
      WHERE id = @id 
        and Name > ''  -- add filter if you think you will have empty strings
    RETURN @r 
END 

Then when you query the data, you will pass a value into the function to concatenate the data into a single row:

select distinct id, dbo.list(id) Names
from yourtable;

See SQL Fiddle with Demo. This gives you a result:

| ID |      NAMES |
-------------------
|  1 |  John, Jim |
|  2 |        Bob |
|  3 | Jane, Bill |
|  4 |       Test |