SQL 2005 合并/将多行连接到一列 [英] SQL 2005 Merge / concatenate multiple rows to one column

问题描述

我们有一些 SQL 难题.假设我有一个看起来像这样的结果......

We have a bit of a SQL quandry. Say I have a results that look like this...

61E77D90-D53D-4E2E-A09E-9D6F012EB59C |
61E77D90-D53D-4E2E-A09E-9D6F012EB59C |乙
61E77D90-D53D-4E2E-A09E-9D6F012EB59C |
61E77D90-D53D-4E2E-A09E-9D6F012EB59C |D
7ce953ca-a55b-4c55-a52c-9d6f012ea903 |乙
7ce953ca-a55b-4c55-a52c-9d6f012ea903 |

61E77D90-D53D-4E2E-A09E-9D6F012EB59C | A
61E77D90-D53D-4E2E-A09E-9D6F012EB59C | B
61E77D90-D53D-4E2E-A09E-9D6F012EB59C | C
61E77D90-D53D-4E2E-A09E-9D6F012EB59C | D
7ce953ca-a55b-4c55-a52c-9d6f012ea903 | E
7ce953ca-a55b-4c55-a52c-9d6f012ea903 | F

有没有一种方法可以将这些结果在 SQL 中分组以作为

is there a way I can group these results within SQL to return as

61E77D90-D53D-4E2E-A09E-9D6F012EB59C |A B C D
7ce953ca-a55b-4c55-a52c-9d6f012ea903 |E F

61E77D90-D53D-4E2E-A09E-9D6F012EB59C | A B C D
7ce953ca-a55b-4c55-a52c-9d6f012ea903 | E F

大家有什么想法吗?

非常感谢

戴夫

推荐答案

我更喜欢定义自定义的用户定义聚合.这是一个 UDA 示例，它将完成非常接近于你在问.

I prefer to define a custom user-defined aggregate. Here's an example of a UDA which will accomplish something very close to what you're asking.

为什么使用用户定义的聚合而不是嵌套的 SELECT?这完全取决于性能，以及您愿意忍受的一切.对于少量元素，您肯定可以使用嵌套的 SELECT，但对于较大的n"，您会注意到查询计划实际上对输出列表中的每一行运行一次嵌套的 SELECT.如果你在谈论大量的行，这可能是死亡之吻.使用 UDA，可以一次性聚合这些值.

Why use a user-defined aggregate instead of a nested SELECT? It's all about performance, and what you are willing to put up with. For a small amount of elements, you can most certainly get away with a nested SELECT, but for large "n", you'll notice that the query plan essentially runs the nested SELECT once for every row in the output list. This can be the kiss of death if you're talking about a large number of rows. With a UDA, it's possible to aggregate these values in a single pass.

当然，权衡是 UDA 要求您使用 CLR 来部署它，而这并不是很多人经常做的事情.在 Oracle 中，这种特殊情况要好一些，因为您可以直接使用 PL/SQL 创建您的用户定义的聚合，但我离题了...

The tradeoff, of course, is that the UDA requires you to use the CLR to deploy it, and that's something not a lot of people do often. In Oracle, this particular situation is a bit nicer as you can use PL/SQL directly to create your user-defined aggregate, but I digress...

这篇关于SQL 2005 合并/将多行连接到一列的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！