问题描述
我发现一种方法来聚集来自不同的行的字符串成一个单一的一行。我期待这样做在很多不同的地方,有这么一个功能,方便这将是很好。我已经使用 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:
-
许多使用
OVER
行和PARTITION
分组,并根据需要为串联排序。其结果是分区
CTE。我们一直行计数每个分区以后过滤结果。
Number the rows using
OVER
andPARTITION
grouping and ordering them as needed for the concatenation. The result isPartitioned
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
这篇关于最佳方式来连接/骨料串的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持!