考虑一个包含名称的数据库表,其中有三行:

Peter
Paul
Mary

有没有一种简单的方法可以把它变成彼得、保罗、玛丽的一串?


当前回答

如果您使用的是SQL Server 2017或Azure,请参阅Mathieu Renda的回答。

当我试图连接两个具有一对多关系的表时,我也遇到了类似的问题。在SQL2005中,我发现XMLPATH方法可以非常容易地处理行的连接。

如果有一个名为STUDENTS的表

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

我期望的结果是:

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

我使用了以下T-SQL:

SELECT Main.SubjectID,
       LEFT(Main.Students,Len(Main.Students)-1) As "Students"
FROM
    (
        SELECT DISTINCT ST2.SubjectID, 
            (
                SELECT ST1.StudentName + ',' AS [text()]
                FROM dbo.Students ST1
                WHERE ST1.SubjectID = ST2.SubjectID
                ORDER BY ST1.SubjectID
                FOR XML PATH (''), TYPE
            ).value('text()[1]','nvarchar(max)') [Students]
        FROM dbo.Students ST2
    ) [Main]

如果您可以在开头插入逗号并使用子字符串跳过第一个逗号,那么您可以以更紧凑的方式执行相同的操作,这样就不需要执行子查询:

SELECT DISTINCT ST2.SubjectID, 
    SUBSTRING(
        (
            SELECT ','+ST1.StudentName  AS [text()]
            FROM dbo.Students ST1
            WHERE ST1.SubjectID = ST2.SubjectID
            ORDER BY ST1.SubjectID
            FOR XML PATH (''), TYPE
        ).value('text()[1]','nvarchar(max)'), 2, 1000) [Students]
FROM dbo.Students ST2

其他回答

在SQL Server vNext中,这将内置STRING_AGG函数。在STRING_AGG(Transact-SQL)中了解有关它的更多信息。

对于Oracle DB,请参阅以下问题:如何在Oracle中将多行连接成一行而不创建存储过程?

最好的答案似乎是@Emmanuel,使用了内置的LISTAGG()函数,该函数在Oracle 11g Release 2和更高版本中提供。

SELECT question_id,
   LISTAGG(element_id, ',') WITHIN GROUP (ORDER BY element_id)
FROM YOUR_TABLE;
GROUP BY question_id

正如@user762952所指出的,根据Oracle的文档http://www.oracle-base.com/articles/misc/string-aggregation-techniques.php,WM_CONCAT()函数也是一个选项。它看起来很稳定,但Oracle明确建议不要将它用于任何应用程序SQL,因此使用时风险自负。

除此之外,您还必须编写自己的函数;上面的Oracle文档提供了如何做到这一点的指南。

虽然为时已晚,而且已经有了许多解决方案。下面是MySQL的简单解决方案:

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

此答案可能会返回意外的结果。要获得一致的结果,请使用其他答案中详细说明的For XML PATH方法之一。

使用COALENCE:

DECLARE @Names VARCHAR(8000) 
SELECT @Names = COALESCE(@Names + ', ', '') + Name 
FROM People

只是一些解释(因为这个答案似乎得到了相对规律的观点):

联合实际上只是一种有助于实现两件事的欺骗:

1) 无需使用空字符串值初始化@Names。

2) 无需在末端去除额外的分隔符。

如果一行具有NULL Name值(如果存在NULL,NULL将使该行之后的@Names为NULL,而下一行将再次以空字符串开始),则上述解决方案将给出错误的结果。使用以下两种解决方案之一即可轻松解决:

DECLARE @Names VARCHAR(8000) 
SELECT @Names = COALESCE(@Names + ', ', '') + Name
FROM People
WHERE Name IS NOT NULL

or:

DECLARE @Names VARCHAR(8000) 
SELECT @Names = COALESCE(@Names + ', ', '') + 
    ISNULL(Name, 'N/A')
FROM People

取决于您想要的行为(第一个选项只是过滤掉NULL,第二个选项用标记消息将它们保留在列表中[用适合您的内容替换“N/a”])。

我并没有做过任何关于性能的分析,因为我的列表中只有不到10项,但在查看了30多个答案后,我感到很惊讶,我仍然对已经给出的类似答案进行了修改,类似于对单个组列表使用COALESCE,甚至不必设置我的变量(无论如何默认为NULL),并且它假设我的源数据表中的所有条目都是非空的:

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

我确信COALENCE内部使用了相同的想法。让我们希望微软不会在我身上改变这一点。