|
我正在寻找一种将不同行中的字符串聚合为一行的方法。我希望在许多不同的地方进行此操作,因此具有促进此操作的功能会很好。我已经尝试过使用COALESCE和解决方案FOR
1 x: t: m1 n: f: I' J2 j0 EXML,但它们只是不适合我。
$ O; G( ~$ V+ L( d B4 O: a! q字符串聚合将执行以下操作:5 M! @+ @# D" V
id | Name Result: id | Names" ~# \/ V, T, @- I' M1 Z) _
-- - ---- -- - -----6 t# M; y: Y$ a2 z
1 | Matt 1 | Matt, Rocks0 f( V3 y! N. T
1 | Rocks 2 | Stylus5 Z. B) l! ^5 [0 l; x' w6 s
2 | Stylus7 X& \' C+ \; g6 y# u. B, v
我看过CLR定义的聚合函数来代替COALESCE和FOR$ s8 b. r* y& l9 c
XML,但是显然 SQL Azure 不 支持CLR定义的东西,这让我很痛苦,因为我知道能够使用它可以解决很多问题。我的问题。
2 ^5 |( h* _" G& w: u& `有什么可能的解决方法,或者类似的最优方法(可能不如CLR最优,但是我会 尽力而为 )来聚合我的东西?6 t! O' m8 ?4 w
+ L1 E% v! M }& p
解决方案:
6 c+ ?1 W; P5 D0 `* U ' E4 ]9 v T' ?; F A% E
' Y* D0 ~, ~3 a4 ~( u9 P+ M8 K" X, w
' _& e# c, z( P1 s; r9 ?1 Y" p 解决方案7 ?! X4 [( c; H4 v$ t2 W! `% o: D
最佳 的定义可能会有所不同,但是这是使用常规Transact SQL将不同行中的字符串连接起来的方法,该方法在Azure中应该可以正常工作。7 c% M1 {+ R" L/ [! `7 g( z+ S
;WITH Partitioned AS
# G3 t$ h/ F) N(9 h2 V$ x; J7 x- B
SELECT , y8 z; m2 Q+ Z/ |6 n" a( O5 t
ID,
! V* z1 d9 E/ F Name,. Y D( w; B4 P8 n3 r
ROW_NUMBER() OVER (PARTITION BY ID ORDER BY Name) AS NameNumber,
# H) ?' o$ p6 @' {! u, `: ` COUNT(*) OVER (PARTITION BY ID) AS NameCount' _+ \" V# {& d. }5 i7 Q5 `
FROM dbo.SourceTable
: i; `0 t% p* d),% |* u+ E+ F- N( z" U2 y2 R
Concatenated AS F5 J0 |0 f: {* |, R. p
(' e6 H% c1 y0 g$ ^/ @4 Y
SELECT ' R1 Q; U) _( w, [/ _! D+ u- }
ID,
8 x2 t) K# L B' V CAST(Name AS nvarchar) AS FullName,
/ `( Z) I" B4 s/ ] H* v2 N Name,
6 D9 e# \0 Z; L4 U/ e) A NameNumber,
0 }' D1 g" l/ g: r, E! I1 G. q6 [ NameCount ' P6 l- [* ^9 p) G/ w' p' Y
FROM Partitioned ; b* C" U$ s, A' |6 |. [
WHERE NameNumber = 1
! N9 p' |" \/ H' i& q7 k1 h; b UNION ALL/ L9 X, y# w; m: c" n; F
SELECT " Z: I2 M5 x2 Q8 l; G& S @ G
P.ID, * |7 \8 z6 i. u" G. ^% H
CAST(C.FullName + ', ' + P.Name AS nvarchar),
! P( y* U& f: X; R P.Name, 6 A' I% ~9 V* G. Z- a. a- [
P.NameNumber,
) t/ L! ]5 F# T- I8 a6 ? ^% n8 X t P.NameCount
8 h' q t$ b: h9 r" A7 L# t FROM Partitioned AS P6 Q' @. o8 Q$ T' t. Y% ]. O% L
INNER JOIN Concatenated AS C
& g+ K, V3 W9 F. G ON P.ID = C.ID , X5 J4 I. i# V; w# Z' f
AND P.NameNumber = C.NameNumber + 12 y% ^2 z! w6 c p V( ~
)
/ h, |% C- u) r9 MSELECT 9 F: V8 p5 a* ]9 x( P
ID,
9 G A7 y% ]4 S( o% N) f FullName9 N* H5 P* x+ ]% g
FROM Concatenated) U+ h$ ] p; D7 E
WHERE NameNumber = NameCount
& ^- U' I5 p4 }' K, S. Y; |) o解释
, w5 Q9 o" g" `) @该方法可归结为三个步骤:4 D N8 K/ x" s9 j& }0 ~; o
[ol], q0 h* {: s# u& ?
使用数字对行进行编号,OVER并PARTITION根据需要对它们进行分组和排序。结果是PartitionedCTE。我们保留每个分区中的行数,以便以后过滤结果。
$ Q, V6 `: k! ?5 x4 K* U- f* j' x
6 F$ z9 w+ J' B使用递归CTE(Concatenated)遍历行号(NameNumber列),将Name值添加到FullName列中。4 n5 W% L5 s% F# }7 p- m
3 A9 ] q8 z: V j- ^4 v
过滤掉所有结果,但结果最高的那些NameNumber。) |7 K3 O5 Y( p' |/ h# x( G
4 Q8 y; J t- Z, ?: N
[/ol]; [# ^2 H6 |8 w `% ]5 ]
请记住,为了使该查询可预测,必须定义分组(例如,在您的方案中具有相同行的行ID)和排序(我假设您只是在串联之前按字母顺序对字符串进行排序)。2 ^0 C& K! q. X5 C* t+ I: S
我已经使用以下数据在SQL Server 2012上快速测试了该解决方案:
( h" O9 L& K% I- [# m. ^INSERT dbo.SourceTable (ID, Name)
6 |- v" ^7 ]( V0 rVALUES 9 \, Q% D) s( B% g
(1, 'Matt'),5 N; g* k @2 P' q7 w' E
(1, 'Rocks'),
2 t) |# k* b; J! h6 G E(2, 'Stylus'),- |8 R/ W9 n7 {' s6 R0 D7 a7 j" y+ b
(3, 'Foo'),$ c" H7 N8 o: L1 m6 D2 @7 G
(3, 'Bar'),# w% B6 |5 H" Y9 W
(3, 'Baz')
# J) T9 z* ~4 T查询结果:3 K/ |; f; x" {5 W1 j+ \
ID FullName+ e( g6 P0 W$ _; r
----------- ------------------------------- y( u/ N# R. k" G- I7 b
2 Stylus2 ?* Y( s4 w5 n7 X( y; e
3 Bar, Baz, Foo
3 E3 o. T( W% G" f1 Matt, Rocks |
|