SQL 如何在sql中连接多个具有相同id的行？
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/35172956/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
How to concatenate many rows with same id in sql?
提问by Luc Le
My table contains the details like with two fields:
我的表格包含两个字段的详细信息：
ID DisplayName
1 Editor
1 Reviewer
7 EIC
7 Editor
7 Reviewer
7 Editor
19 EIC
19 Editor
19 Reviewer
I want get the unique details with DisplayName like
我想使用 DisplayName 获取独特的详细信息，例如
1 Editor,Reviewer 7 EIC,Editor,Reviewer
1 Editor,Reviewer 7 EIC,Editor,Reviewer
Don't get duplicate value with ID 7
不要获得 ID 为 7 的重复值
How to combine DisplayName Details? How to write the Query?
如何结合 DisplayName 详细信息？查询语句怎么写？
回答by Stanislovas Kala?nikovas
In SQL-Serveryou can do it in the following:
在SQL-Server 中，您可以通过以下方式执行此操作：
QUERY
询问
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
TEST DATA
测试数据
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')
OUTPUT
输出
id displayname
1 Editor, Reviewer
7 Editor, EIC, Reviewer
19 Editor, EIC, Reviewer
回答by Devart
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
Output -
输出 -
----------- ------------------------
1 Editor, Reviewer
7 Editor, EIC, Reviewer
19 Editor, EIC, Reviewer
My post about string aggregation:
我关于字符串聚合的帖子：
http://www.codeproject.com/Articles/691102/String-Aggregation-in-the-World-of-SQL-Server
http://www.codeproject.com/Articles/691102/String-Aggregation-in-the-World-of-SQL-Server
回答by Monami J
For MySQL:SELECT id, GROUP_CONCAT(displayname) FROM tableName GROUP BY id
对于 MySQL：SELECT id, GROUP_CONCAT(displayname) FROM tableName GROUP BY id
回答by venkat
and in case of oracle database
如果是 oracle 数据库
select id,
listagg(displayname, ',') within group (order by displayname) as names
from test
group by id
回答by Luc Le
Thank you all,
谢谢你们，
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
