如何在SQL Server中将多行文本连接成单个文本字符串

Question

如何在SQL Server中将多行文本连接成单个文本字符串

sqlsql-servercsvstring-concatenationgroup-concat

2398

考虑一个包含姓名的数据库表格，有三行：

Peter
Paul
Mary

有没有简单的方法将这些内容转换成一个字符串：Peter, Paul, Mary？

- John

29

如需针对SQL Server的特定答案，请尝试此问题。 - Matt Hamilton

22

对于MySQL，请查看此答案中的Group_Concat。它可以将多行合并为一个字段。 - Pykler

31

希望SQL Server的下一个版本能够添加一项新功能，以优雅地解决多行字符串连接的问题，而不需要使用 FOR XML PATH 这种繁琐方式。 - Pete Alvin

4

不是SQL，但如果这只是一次性的事情，你可以将列表粘贴到这个在线工具convert.town/column-to-comma-separated-list中进行转换。 - Stack Man

4

在Oracle数据库中，你可以在11g r2版本之后使用LISTAGG(COLUMN_NAME)函数，它可以实现与旧版中不被支持的WM_CONCAT(COLUMN_NAME)函数相同的功能。 - Richard

显示剩余5条评论

48个回答

网页内容由stack overflow 提供, 点击上面的

可以查看英文原文，
原文链接

- endo64 · Answer 1

这也可以很有用

create table #test (id int,name varchar(10))
--use separate inserts on older versions of SQL Server
insert into #test values (1,'Peter'), (1,'Paul'), (1,'Mary'), (2,'Alex'), (3,'Jack')

DECLARE @t VARCHAR(255)
SELECT @t = ISNULL(@t + ',' + name, name) FROM #test WHERE id = 1
select @t
drop table #test

返回值

Peter,Paul,Mary

- Graeme · Answer 2

SQL Server 2005或更高版本

CREATE TABLE dbo.Students
(
    StudentId INT
    , Name VARCHAR(50)
    , CONSTRAINT PK_Students PRIMARY KEY (StudentId)
);

CREATE TABLE dbo.Subjects
(
    SubjectId INT
    , Name VARCHAR(50)
    , CONSTRAINT PK_Subjects PRIMARY KEY (SubjectId)
);

CREATE TABLE dbo.Schedules
(
    StudentId INT
    , SubjectId INT
    , CONSTRAINT PK__Schedule PRIMARY KEY (StudentId, SubjectId)
    , CONSTRAINT FK_Schedule_Students FOREIGN KEY (StudentId) REFERENCES dbo.Students (StudentId)
    , CONSTRAINT FK_Schedule_Subjects FOREIGN KEY (SubjectId) REFERENCES dbo.Subjects (SubjectId)
);

INSERT dbo.Students (StudentId, Name) VALUES
    (1, 'Mary')
    , (2, 'John')
    , (3, 'Sam')
    , (4, 'Alaina')
    , (5, 'Edward')
;

INSERT dbo.Subjects (SubjectId, Name) VALUES
    (1, 'Physics')
    , (2, 'Geography')
    , (3, 'French')
    , (4, 'Gymnastics')
;

INSERT dbo.Schedules (StudentId, SubjectId) VALUES
    (1, 1)        --Mary, Physics
    , (2, 1)    --John, Physics
    , (3, 1)    --Sam, Physics
    , (4, 2)    --Alaina, Geography
    , (5, 2)    --Edward, Geography
;

SELECT
    sub.SubjectId
    , sub.Name AS [SubjectName]
    , ISNULL( x.Students, '') AS Students
FROM
    dbo.Subjects sub
    OUTER APPLY
    (
        SELECT
            CASE ROW_NUMBER() OVER (ORDER BY stu.Name) WHEN 1 THEN '' ELSE ', ' END
            + stu.Name
        FROM
            dbo.Students stu
            INNER JOIN dbo.Schedules sch
                ON stu.StudentId = sch.StudentId
        WHERE
            sch.SubjectId = sub.SubjectId
        ORDER BY
            stu.Name
        FOR XML PATH('')
    ) x (Students)
;

- Ravi Pipaliya · Answer 3

这里是实现此目标的完整解决方案：

-- Table Creation
CREATE TABLE Tbl
( CustomerCode    VARCHAR(50)
, CustomerName    VARCHAR(50)
, Type VARCHAR(50)
,Items    VARCHAR(50)
)

insert into Tbl
SELECT 'C0001','Thomas','BREAKFAST','Milk'
union SELECT 'C0001','Thomas','BREAKFAST','Bread'
union SELECT 'C0001','Thomas','BREAKFAST','Egg'
union SELECT 'C0001','Thomas','LUNCH','Rice'
union SELECT 'C0001','Thomas','LUNCH','Fish Curry'
union SELECT 'C0001','Thomas','LUNCH','Lessy'
union SELECT 'C0002','JOSEPH','BREAKFAST','Bread'
union SELECT 'C0002','JOSEPH','BREAKFAST','Jam'
union SELECT 'C0002','JOSEPH','BREAKFAST','Tea'
union SELECT 'C0002','JOSEPH','Supper','Tea'
union SELECT 'C0002','JOSEPH','Brunch','Roti'

-- function creation
GO
CREATE  FUNCTION [dbo].[fn_GetItemsByType]
(   
    @CustomerCode VARCHAR(50)
    ,@Type VARCHAR(50)
)
RETURNS @ItemType TABLE  ( Items VARCHAR(5000) )
AS
BEGIN

        INSERT INTO @ItemType(Items)
    SELECT  STUFF((SELECT distinct ',' + [Items]
         FROM Tbl 
         WHERE CustomerCode = @CustomerCode
            AND Type=@Type
            FOR XML PATH(''))
        ,1,1,'') as  Items



    RETURN 
END

GO

-- fianl Query
DECLARE @cols AS NVARCHAR(MAX),
    @query  AS NVARCHAR(MAX)

select @cols = STUFF((SELECT distinct ',' + QUOTENAME(Type) 
                    from Tbl
            FOR XML PATH(''), TYPE
            ).value('.', 'NVARCHAR(MAX)') 
        ,1,1,'')

set @query = 'SELECT CustomerCode,CustomerName,' + @cols + '
             from 
             (
                select  
                    distinct CustomerCode
                    ,CustomerName
                    ,Type
                    ,F.Items
                    FROM Tbl T
                    CROSS APPLY [fn_GetItemsByType] (T.CustomerCode,T.Type) F
            ) x
            pivot 
            (
                max(Items)
                for Type in (' + @cols + ')
            ) p '

execute(@query)

- topchef · Answer 4

该方法仅适用于Teradata Aster数据库，因为它使用其NPATH函数。

同样，我们有一个名为Students的表格。

SubjectID       StudentName
----------      -------------
1               Mary
1               John
1               Sam
2               Alaina
2               Edward

然后使用NPATH只需要单个SELECT：

SELECT * FROM npath(
  ON Students
  PARTITION BY SubjectID
  ORDER BY StudentName
  MODE(nonoverlapping)
  PATTERN('A*')
  SYMBOLS(
    'true' as A
  )
  RESULT(
    FIRST(SubjectID of A) as SubjectID,
    ACCUMULATE(StudentName of A) as StudentName
  )
);

结果：

SubjectID       StudentName
----------      -------------
1               [John, Mary, Sam]
2               [Alaina, Edward]

- Pooja Bhat · Answer 5

以下是使用“基本循环”和“rownum”实现给定场景的简单PL/SQL过程

表定义

CREATE TABLE "NAMES" ("NAME" VARCHAR2(10 BYTE))) ;

让我们向这个表中插入值。

INSERT INTO NAMES VALUES('PETER');
INSERT INTO NAMES VALUES('PAUL');
INSERT INTO NAMES VALUES('MARY');

程序从这里开始

DECLARE 

MAXNUM INTEGER;
CNTR INTEGER := 1;
C_NAME NAMES.NAME%TYPE;
NSTR VARCHAR2(50);

BEGIN

SELECT MAX(ROWNUM) INTO MAXNUM FROM NAMES;

LOOP

SELECT NAME INTO  C_NAME FROM 
(SELECT ROWNUM RW, NAME FROM NAMES ) P WHERE P.RW = CNTR;

NSTR := NSTR ||','||C_NAME;
CNTR := CNTR + 1;
EXIT WHEN CNTR > MAXNUM;

END LOOP;

dbms_output.put_line(SUBSTR(NSTR,2));

END;

结果

PETER,PAUL,MARY

- Glen · Answer 6

虽然我的列表中不到10个项目，我并没有对性能进行任何分析，但当我浏览了30多个答案后，我仍然对已经给出的相似答案有了新的想法，类似于使用COALESCE来处理单个组列表，并且甚至不需要设置变量（默认为NULL），它假定源数据表中的所有条目都是非空的：

DECLARE @MyList VARCHAR(1000), @Delimiter CHAR(2) = ', '
SELECT @MyList = CASE WHEN @MyList > '' THEN @MyList + @Delimiter ELSE '' END + FieldToConcatenate FROM MyData

我相信COALESCE内部使用了相同的思路。希望微软不要对此进行更改。

- Manu · Answer 7

在SQL Server中，一种方法是将表内容作为XML返回（使用for XML raw），将结果转换为字符串，然后用", "替换标签。

- panser · Answer 8

在PostgreSQL中，array_agg函数用于将行组合成数组。

SELECT array_to_string(array_agg(DISTINCT rolname), ',') FROM pg_catalog.pg_roles;

或者STRING_AGG函数

SELECT STRING_AGG(rolname::text,',') FROM pg_catalog.pg_roles;

- Shahbaz · Answer 9

1

虽然现在已经有很多解决方案，但这里提供一个简单的MySQL解决方案：

SELECT t1.id,
        GROUP_CONCAT(t1.id) ids
 FROM table t1 JOIN table t2 ON (t1.id = t2.id)
 GROUP BY t1.id

- Shahbaz

1

这个问题是针对SQL Server的，因此需要它的人不太可能找到这个答案。是否有关于同样事情的MySQL特定问题？ - jpaugh

@jpaugh：这并不是借口，但第四个答案（如果删除的答案不计算，则为第二个），在第二天发布，获得了140个赞，也是关于MySQL的：Darryl Hein的答案。其他答案是针对Oracle和PostgreSQL的。最好的方法是什么？将它们标记为“不是答案”？还是其他什么？ - Peter Mortensen

- Esperento57 · Answer 10

使用递归查询，您可以完成它：

-- Create example table
CREATE TABLE tmptable (NAME VARCHAR(30)) ;

-- Insert example data
INSERT INTO tmptable VALUES('PETER');
INSERT INTO tmptable VALUES('PAUL');
INSERT INTO tmptable VALUES('MARY');

-- Recurse query
with tblwithrank as (
select * , row_number() over(order by name) rang , count(*) over() NbRow
from tmptable
),
tmpRecursive as (
select *, cast(name as varchar(2000)) as AllName from tblwithrank  where rang=1
union all
select f0.*,  cast(f0.name + ',' + f1.AllName as varchar(2000)) as AllName 
from tblwithrank f0 inner join tmpRecursive f1 on f0.rang=f1.rang +1 
)
select AllName from tmpRecursive
where rang=NbRow