|
我正在寻找一种将不同行中的字符串聚合为一行的方法。我希望在许多不同的地方进行此操作,因此具有促进此操作的功能会很好。我已经尝试过使用COALESCE和解决方案FOR7 f: k6 H$ ~5 o4 h! l6 V5 |) V
XML,但它们只是不适合我。; f w' P8 v+ r
字符串聚合将执行以下操作:
( B! ~& v3 `. O& \3 t3 t2 L' L$ p% Uid | Name Result: id | Names
: @1 P0 M `8 e$ M-- - ---- -- - -----: J+ T4 t/ c7 H6 ^. H- p
1 | Matt 1 | Matt, Rocks" ]: i& y) O! b6 |- ~: h
1 | Rocks 2 | Stylus
% B6 U6 I, t% t D2 | Stylus
3 P* N6 a5 F! V* `4 {* r我看过CLR定义的聚合函数来代替COALESCE和FOR- ^* u3 V' K& H1 L
XML,但是显然 SQL Azure 不 支持CLR定义的东西,这让我很痛苦,因为我知道能够使用它可以解决很多问题。我的问题。
8 m% |# A! ]3 p% L/ e _; q有什么可能的解决方法,或者类似的最优方法(可能不如CLR最优,但是我会 尽力而为 )来聚合我的东西?
A9 |$ }# t* }4 j) C1 U, V9 ~0 e
( D4 g' K4 D. ~7 R# g' M: y8 L解决方案:& E+ M' k" b. d+ j# `' P! f
! _, Z: K8 Z. U9 U1 l; \
% L9 D: M+ Y0 A. a6 p- P, S# Q0 d4 o) r# k! {; L
解决方案 ` L/ h6 C- B4 z9 ?' w2 u$ ]1 f
最佳 的定义可能会有所不同,但是这是使用常规Transact SQL将不同行中的字符串连接起来的方法,该方法在Azure中应该可以正常工作。# ?( }$ Y. e; L, \
;WITH Partitioned AS2 L: y. U. h5 v4 e. g2 G
(
' z* }- ? ]. ~ SELECT ! \, ]) b7 D( A4 j' z8 O5 e
ID,
6 c6 N5 |: \" ]6 y& U Name,
7 G( {+ |9 f: H& m7 ~# r& `& y ROW_NUMBER() OVER (PARTITION BY ID ORDER BY Name) AS NameNumber,3 k" G, W. t5 u. t) t0 u% O
COUNT(*) OVER (PARTITION BY ID) AS NameCount9 L. j5 V5 F! p2 m
FROM dbo.SourceTable0 z5 m: r1 |6 i1 i* P `
),
- ^- C8 Z$ Z \% ~Concatenated AS
- C0 P+ d7 t& |9 d) V( j(5 P! t. K( @. x# _* _) k% L8 z
SELECT
6 `3 r+ w! a8 r ID, 0 a- _5 j* {2 s/ K
CAST(Name AS nvarchar) AS FullName,
3 l. X+ R) R, h Name, ) c1 x7 Y1 Z5 v2 O9 O# y, f
NameNumber, 6 Y- l* n" w3 d# h9 a
NameCount
: F1 t7 b) U4 C5 T' p8 o FROM Partitioned 1 v4 O l4 p& F4 ~, L" W! o
WHERE NameNumber = 1
! [2 \1 B& u0 W0 [ UNION ALL
! u) y t) i0 ?* ]5 E* z: n# f SELECT 7 {6 I* n b/ l
P.ID,
- H% }) |, N( L9 |" K CAST(C.FullName + ', ' + P.Name AS nvarchar), - K3 F6 d$ L2 W9 e6 [
P.Name, ( y5 p. O+ ^# v0 q. w
P.NameNumber,
3 U# v. q+ n3 v. g6 K P.NameCount1 S; J+ |8 Z r" W& p
FROM Partitioned AS P1 T" D6 |+ N& Z1 a$ d; J& {
INNER JOIN Concatenated AS C
O- z$ F: H( v" u# z" r ON P.ID = C.ID . T3 S5 F7 k- P! D/ S% \. n2 ~
AND P.NameNumber = C.NameNumber + 1, M/ ?0 W- N' ~4 t! |, B+ S9 W
)5 d" H1 p6 k1 ]4 _! O' ?% O; U D- f
SELECT
& c% P5 \2 D" E+ }2 g p- B8 E ID,7 t) o' x/ G1 u
FullName
+ y0 r. H. W$ {FROM Concatenated
" S6 ~& J+ Z: p6 \% ?8 fWHERE NameNumber = NameCount4 X. p/ D3 u! g* T# }) v
解释
3 Z& Q# F7 K* h( c% `该方法可归结为三个步骤:
/ s! y, C5 e3 G* b! \4 F[ol]& c* |' F; g: o9 C) E
使用数字对行进行编号,OVER并PARTITION根据需要对它们进行分组和排序。结果是PartitionedCTE。我们保留每个分区中的行数,以便以后过滤结果。8 o4 B$ }: V. m0 P4 Y( x
( I$ I/ P3 Z, ~3 R/ N4 T& G0 @: y/ F
使用递归CTE(Concatenated)遍历行号(NameNumber列),将Name值添加到FullName列中。
/ C* d$ Q! f% n& X" S7 w
# H1 }- W* v- A$ |, ]. P# a! a过滤掉所有结果,但结果最高的那些NameNumber。% }+ ?+ L3 L5 I8 }0 e. d: B
# c/ \ i1 W, O. t& i9 q0 w7 ]
[/ol]
+ B/ @2 f" o* K! n/ P4 Z0 b/ q! w请记住,为了使该查询可预测,必须定义分组(例如,在您的方案中具有相同行的行ID)和排序(我假设您只是在串联之前按字母顺序对字符串进行排序)。% k( w! \( X9 d# T
我已经使用以下数据在SQL Server 2012上快速测试了该解决方案:
' z, r# E! Z1 K* ]( Y* w k4 l& LINSERT dbo.SourceTable (ID, Name)
' X/ l- t5 ]( Q. B& f8 P4 DVALUES 7 ~: E9 F: E" A. H- o3 A* o. W
(1, 'Matt'),
2 Y: \% e+ j/ } c# p3 e7 _) e(1, 'Rocks'),
$ r: r- s3 \4 ]) H(2, 'Stylus'),
3 } i7 m) ]* P1 v$ ^4 `+ H(3, 'Foo'),
; W @4 `8 N3 b2 \(3, 'Bar'),4 d1 K1 A2 v7 n3 X4 e
(3, 'Baz')+ \* R1 N8 X4 z7 m* @
查询结果:
j# [, p2 ~0 |+ y1 `! A( KID FullName
' V2 e' C2 K% [* w: ^# @6 b----------- ------------------------------3 \, R6 G K" Z. V' \/ X6 F# `5 P
2 Stylus
: K& {' U- _( ?( C7 J3 Bar, Baz, Foo" A7 C# V, p6 d# [% a, \7 O/ ^9 H$ b
1 Matt, Rocks |
|