连接表中的行 [英] Concatenate Rows in a table

问题描述

我有一张桌子如下

I Have a table as below

id   suborder
==================
1    a
2    b
1    c
1    d
2    e
3    f

我需要一个结果集如下

and i need a resultset as below

id   suborder    OtherSuborders
===============================
1     a         c,d,
2     b         e
1     c         a,d
1     d         a,c
2     e         b
3     f         null (0r) empty

任何人都能帮帮我吗？



提前谢谢。

Can Any one help me out?

Thanks in advance.

推荐答案

您可以使用FOR XML PATH（）函数连接表中的行。看看这个：http：//sqlandme.com/2011/04/27/tsql-concatenate-rows-using-for-xml-path/

You can concatenate rows in a table using FOR XML PATH() function. Check this out: http://sqlandme.com/2011/04/27/tsql-concatenate-rows-using-for-xml-path/

尝试如下，

Hi,
Try like as follows,

SELECT ROW_NUMBER() OVER(PARTITION BY M.id, ORDER BY M.suborder) 'RowNo',
M.id, M.suborder, STUFF((SELECT ','+suborder AS text()
FROM table_name
WHERE ROW_NUMBER() OVER(PARTITION BY id, ORDER BY suborder) <> 1
AND id=M.ID FOR XML PATH(''))),1,1,'') 'OtherSuborders'
FROM table_name M
WHERE ROW_NUMBER() OVER(PARTITION BY M.id, ORDER BY M.suborder) = 1
-- In my PC , I don't have SQL Server, So check and tel me the give Query is fine or not.

问候，

GVPrabu

Regards,
GVPrabu

您需要的是理论连接聚合。嗯，问题是，SQL Server 2008R2中没有这样的东西。但好的新功能是，您可以通过CLR集成轻松制作一个（请参阅： http://msdn.microsoft.com/en-us/library/91e6taax(v = vs.80）.aspx [ ^ ]和 http://msdn.microsoft.com/en-us/library/ms182741（v = SQL.105）的.aspx [< a href =http://msdn.microsoft.com/en-us/library/ms182741(v=sql.105).aspx\"target =_ blanktitle =New Window> ^ ]） 。

这是一个非常简单明了的解决方案，但你需要两件事情：编写代码（非常简单），在数据库中部署和声明聚合（为此你需要在服务器上拥有管理权限） ）。

请看这里： http：// www.mssqltips.com/sqlservertip/2022/concat-aggregates-sql-server-clr-function/ [ ^ ]

本主题中另一个有趣的读物：https://www.simple-talk.com/sql/t-sql-编程/连接-row-values-in-transact-sql / [ ^ ]

What you need is a theoretical concatenation aggregate. Well, the problem is, that there is no such thing in SQL Server 2008R2. But the good new is, that you can make one really easily with the CLR integration (see: http://msdn.microsoft.com/en-us/library/91e6taax(v=vs.80).aspx[^] and http://msdn.microsoft.com/en-us/library/ms182741(v=sql.105).aspx[^]).
It is a really clear and simple solution but you need two things as above: write the code (really simple), deploy and declare the aggregate in the database (for this you need administrative privileges on the server).
Look here: http://www.mssqltips.com/sqlservertip/2022/concat-aggregates-sql-server-clr-function/[^]
An other interesting reading in this topic: https://www.simple-talk.com/sql/t-sql-programming/concatenating-row-values-in-transact-sql/[^]

这篇关于连接表中的行的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！