本文介绍了最佳方式来连接/骨料串的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着小编来一起学习吧!

问题描述

我发现一种方法来聚集来自不同的行的字符串成一个单一的一行。我期待这样做在很多不同的地方,有这么一个功能,方便这将是很好。我已经使用 COALESCE FOR XML 尝试解决办法,但他们只是不削减对我来说。

I'm finding a way to aggregate strings from different rows into a single row. I'm looking to do this in many different places, so having a function to facilitate this would be nice. I've tried solutions using COALESCE and FOR XML, but they just don't cut it for me.

字符串聚集会做这样的事情:

String aggregation would do something like this:

id | Name                    Result: id | Names
-- - ----                            -- - -----
1  | Matt                            1  | Matt, Rocks
1  | Rocks                           2  | Stylus
2  | Stylus

我已经采取了看CLR-defined聚合函数中为 COALESCE替换 FOR XML ,但显然 SQL Azure的的支持CLR定义的东西,这是一种痛苦对我来说,因为我知道能够使用它可以解决一大堆问题给我。

I've taken a look at CLR-defined aggregate functions as a replacement for COALESCE and FOR XML, but apparently SQL Azure does not support CLR-defined stuff, which is a pain for me because I know being able to use it would solve a whole lot of problems for me.

是否有任何可能的解决方法,或类似的最佳方法(它可能不会像CLR为最优的,但的的我会采取什么我可以得到),我可以用它来聚集我的东西?

Is there any possible workaround, or similarly optimal method (which might not be as optimal as CLR, but hey I'll take what I can get) that I can use to aggregate my stuff?

推荐答案

SOLUTION

的定义的优化的可以不同,但​​这里是如何连接使用常规的Transact SQL不同行的字符串,应在Azure中很好地工作。

The definition of optimal can vary, but here's how to concatenate strings from different rows using regular Transact SQL, which should work fine in Azure.

;WITH Partitioned AS
(
    SELECT
        ID,
        Name,
        ROW_NUMBER() OVER (PARTITION BY ID ORDER BY Name) AS NameNumber,
        COUNT(*) OVER (PARTITION BY ID) AS NameCount
    FROM dbo.SourceTable
),
Concatenated AS
(
    SELECT ID, CAST(Name AS nvarchar) AS FullName, Name, NameNumber, NameCount FROM Partitioned WHERE NameNumber = 1

    UNION ALL

    SELECT
        P.ID, CAST(C.FullName + ', ' + P.Name AS nvarchar), P.Name, P.NameNumber, P.NameCount
    FROM Partitioned AS P
        INNER JOIN Concatenated AS C ON P.ID = C.ID AND P.NameNumber = C.NameNumber + 1
)
SELECT
    ID,
    FullName
FROM Concatenated
WHERE NameNumber = NameCount

说明

该方法可以归结为三个步骤:

The approach boils down to three steps:


  1. 许多使用 OVER 行和 PARTITION 分组,并根据需要为串联排序。其结果是分区 CTE。我们一直行计数每个分区以后过滤结果。

  1. Number the rows using OVER and PARTITION grouping and ordering them as needed for the concatenation. The result is Partitioned CTE. We keep counts of rows in each partition to filter the results later.

使用递归CTE(级联)通过行号迭代( NameNumber 列)添加名称全名列。

Using recursive CTE (Concatenated) iterate through the row numbers (NameNumber column) adding Name values to FullName column.

过滤掉所有的结果,但与个位的最高 NameNumber

Filter out all results but the ones with the highest NameNumber.

请记住,为了使这个查询predictable一个人来定义分组(例如,在同一个 ID您的方案行是级联)和排序(我以为你只是在连接前字母串排序)。

Please keep in mind that in order to make this query predictable one has to define both grouping (for example, in your scenario rows with the same ID are concatenated) and sorting (I assumed that you simply sort the string alphabetically before concatenation).

我赶紧测试的SQL Server 2012的解决方案具有以下数据:

I've quickly tested the solution on SQL Server 2012 with the following data:

INSERT dbo.SourceTable (ID, Name)
VALUES
(1, 'Matt'),
(1, 'Rocks'),
(2, 'Stylus'),
(3, 'Foo'),
(3, 'Bar'),
(3, 'Baz')

查询结果:

ID          FullName
----------- ------------------------------
2           Stylus
3           Bar, Baz, Foo
1           Matt, Rocks

这篇关于最佳方式来连接/骨料串的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持!

09-06 00:39
查看更多