SQL Server使用多对多相关表连接行 [英] SQL Server Concatenate Rows Using Many-To-Many Related Tables

问题描述

我已经搜索了我的问题，但是大多数示例(如果不是全部)都必须处理一个或两个表，它们之间是一对多的关系. 所以，这是我的情况:

I've already searched for my problem, but most of the examples (if not all), have to deal with only one or two tables with a one-to-many relationship between them. So, here goes my scenario:

我的第一张桌子是:

OrderID Quantity    Price
----------------------------------
18      1000.00     160.00
19      1000.00     40.00
22      1000.00     40.00
23      100.00      500.00
24      10.00       50.00

我的第二张桌子是:

ExtrasID    Name
-------------------
1           Value 1
2           Value 2
3           Value 3
4           Value 4
5           Value 5

我在上面的表之间建立了多对多关系，因此有第三个(联合)表，如下所示:

I have a many-to-many relationship established between the tables above, so there is a third (joint) table which is as follows:

OrderExtrassID  OrderExtras_OrderID OrderExtras_ExtrasID
----------------------------------------------------------------
20              19                  2
22              22                  3
23              23                  2
24              23                  5

现在，我要做的就是获得如下结果:

Now, all I want to achieve is to get a result such as the following:

OrderID Extras
----------------------------
18      NULL
19      Value 2
22      Value 3
23      Value 2, Value 5
24      NULL

有很多使用XML PATH，PIVOT，CTE等的示例，但是这些似乎都无法帮助我解决我的情况，或者至少我没有深入研究它们以完成工作...

There are many examples using XML PATH, PIVOT, CTE etc., but none of these seems to help me with my scenario, or at least I've not managed to study them in depth, in order to get my work done...

预先感谢

推荐答案

我建议使用For XML将多行返回为一行，然后使用STUFF删除多余的逗号.像这样:

I would suggest using For XML to return multiple rows into a single row and then using STUFF to removing the extra comma. Something like this:

SELECT O.OrderId
 ,STUFF(
        (
            SELECT ',' + E.Name AS [text()]
                FROM OrderExtras OE
                    Inner Join Extras E 
                        ON E.ExtrasId = OE.OrderExtras_ExtrasID
                WHERE O.OrderId = OE.OrderExtras_OrderID
            ORDER BY E.Name
            FOR XML PATH('')
        ), 1, 1, '') AS ColList
FROM Orders O

这是 SQL小提琴.

祝你好运.

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