如何在SQL中将具有相同ID的多行连接起来？

Question

我的表格包含两个字段，分别是：

ID      DisplayName
1        Editor
1        Reviewer
7        EIC
7        Editor
7        Reviewer
7        Editor
19       EIC
19       Editor
19       Reviewer

我想获取像DisplayName这样的唯一详情

1 Editor，Reviewer 7 EIC，Editor，Reviewer

不要获取具有ID 7的重复值

如何合并DisplayName详情？如何编写查询？

- Luc Le

---

它是哪一个？mysql还是sql-server？ - sagi

@sagi：我指的是SQL Server。 - Luc Le

你现在想要有2列吗？ID和DisplayName只是为了同一个id，你想要逗号分隔的名字吗？或者你想要像你写的那样将所有结果放在一行中？ - Stanislovas Kalašnikovas

@StanislovasKalašnikovas：我想获取所有结果，但我不需要获取重复的值。ID = 7 我有2个编辑器，但我只想获取一个。 - Luc Le

可能是如何在SQL Server中使用GROUP BY连接字符串？的重复问题。 - sagi

Answer 1

在SQL-Server中，您可以按照以下方式进行操作：

查询。

SELECT id, displayname = 
    STUFF((SELECT DISTINCT ', ' + displayname
           FROM #t b 
           WHERE b.id = a.id 
          FOR XML PATH('')), 1, 2, '')
FROM #t a
GROUP BY id

测试数据

create table #t 
(
id int,
displayname nvarchar(max)
)

insert into #t values    
 (1 ,'Editor')
,(1 ,'Reviewer')
,(7 ,'EIC')
,(7 ,'Editor')
,(7 ,'Reviewer')
,(7 ,'Editor')
,(19,'EIC')
,(19,'Editor')
,(19,'Reviewer')

输出

id  displayname
1   Editor, Reviewer
7   Editor, EIC, Reviewer
19  Editor, EIC, Reviewer

- Stanislovas Kalašnikovas

---

“after the stuff” 后面的 , 1, 2, '' 是什么意思？ - guradio

@guradio 这是为了去除原本会出现的逗号和空格。它们是 STUFF 的参数：起始位置 = 1，长度 = 2，替换值 = ''，即前两个字符被替换为''。 - bernhof

Answer 2

SQL Server 2017+和SQL Azure: STRING_AGG

从下一个版本的SQL Server开始，我们终于可以在不使用任何变量或XML技巧的情况下跨行连接。

STRING_AGG (Transact-SQL)

SELECT ID, STRING_AGG(DisplayName, ', ') AS DisplayNames
FROM TableName
GROUP BY ID

- Jayrag Pareek

---

如何使用这种方法避免重复。例如，在ID 7中，如何将EIC，编辑，评审人员，编辑的重复项改为EIC，编辑，评审人员，而不重复编辑？ - undefined

Answer 3

DECLARE @t TABLE
(
    ID INT,
    DisplayName VARCHAR(50)
)
INSERT INTO @t (ID, DisplayName)
VALUES
    (1 , 'Editor'),
    (1 , 'Reviewer'),
    (7 , 'EIC'),
    (7 , 'Editor'),
    (7 , 'Reviewer'),
    (7 , 'Editor'),
    (19, 'EIC'),
    (19, 'Editor'),
    (19, 'Reviewer')

SELECT *, STUFF((
            SELECT DISTINCT ', ' + DisplayName
            FROM @t
            WHERE ID = t.ID
            FOR XML PATH('')), 1, 2, '')
FROM (
    SELECT DISTINCT ID
    FROM @t
) t

输出 -

----------- ------------------------
1           Editor, Reviewer
7           Editor, EIC, Reviewer
19          Editor, EIC, Reviewer

关于字符串聚合的文章：

http://www.codeproject.com/Articles/691102/String-Aggregation-in-the-World-of-SQL-Server

该文章介绍了如何在SQL Server中实现字符串聚合，并提供了多种方法和示例。如果您需要在数据库中将多行数据组合成单个字符串，这篇文章会对您有所帮助。

- Devart

---

在编程中，“stuff”后面的, 1, 2, ''是什么意思？ - guradio

终于用这个示例使它工作了。只需用类似临时表的子查询替换@t，它就可以正常工作！ - Simon Dufour

Answer 4

针对MySQL的情况：
SELECT id, GROUP_CONCAT(displayname) FROM tableName GROUP BY id

参考：http://www.sqlines.com/mysql/functions/group_concat

- Monami J

---

SQL-Server 不支持这种表示法。 - vahdet

谢谢，非常好用。但愿我们能够更改分隔符！ - rdonuk

Answer 5

而在 Oracle 数据库的情况下

select id, 
       listagg(displayname, ',') within group (order by displayname) as names
from test
group by id 

- venkat

Answer 6

要更改分隔符，请使用

SELECT id, GROUP_CONCAT(displayname SEPARATOR ';') FROM tableName GROUP BY id

这将把分隔符从逗号改为分号 :)

- Abhishek Tomar

Answer 7

谢谢大家，

SELECT Distinct
    t1.ID,
    MAX(STUFF(t2.x_id,1,1,'')) AS DisplayName
FROM Table t1
CROSS apply(
    SELECT Distinct ', ' + SUBSTRING(t2.DisplayName,1,2)
    FROM Table t2
    WHERE t2.ID = t1.ID AND t2.DisplayName > ''
    FOR xml PATH('')
) AS t2 (x_id)
GROUP BY
    t1.ID
order by 1
GO

- Luc Le

---

只是让你知道。当表格中有很多行时，这种方法比Devart和Stan的答案慢得多。 - t-clausen.dk

在编程中，“stuff”后面的, 1, 2, ''是什么意思？ - guradio

Answer 8

在Oracle SQL中，您可以使用LISTAGG。

SELECT ID, LISTAGG(DisplayName, ', ') as all_agg
FROM TABLE_NAME GROUP BY ID;

- Sreekant Shenoy