如何获得以下sql查询的结果？

My table like this:

我的桌子是这样的:

id name 
1  jid1
1  jid2
2  jid1
2  jid3

How to get matched id values, in above table jid1,jid2 have same id and jid1,jid3 have another same id value.

如何获得匹配的id值,在上面的表jid1中,jid2具有相同的id和jid1,jid3具有另一个相同的id值。

How to get those matched records? I need how to write query for that?

如何获得那些匹配的记录?我需要如何编写查询?

Expected OUTPUT:

id name
1 jid1,jid2
2 jid1,jid3

1 个解决方案

#1

You can use the FOR XML to do so, like this:

您可以使用FOR XML来执行此操作,如下所示:

SELECT 
  t1.Id,
  STUFF((
    SELECT ', ' + t2.name
    FROM Tablename t2
    WHERE t2.ID   = t1.ID
    FOR XML PATH (''))
  ,1,2,'') AS Name
FROM Tablename t1
GROUP BY t1.Id;

• SQL Fiddle Demo

SQL小提琴演示