SQL Server 2000的GROUP_CONCAT函数 [英] SQL Server 2000 equivalent of GROUP_CONCAT function

问题描述

我试图在SQL Server 2000中使用GROUP_CONCAT函数，但它返回错误:

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

'group_concat'不是公认的函数名称"

'group_concat' is not a recognized function name"

所以我想在SQL Server 2000中还有group_concat的其他功能吗?你能告诉我这是什么吗?

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

推荐答案

不幸的是，由于您使用的是SQL Server 2000，因此无法使用FOR XML PATH将这些值连接在一起.

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;

请参见带有演示的SQL小提琴.这样会为您提供结果:

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

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

这篇关于SQL Server 2000的GROUP_CONCAT函数的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！