|
我试图创建一个SELECT查询,使我可以将联系人的多个电话号码合并到单独的列中。
1 b% e3 f' V; }# |$ s9 L) ~; p例如,我想要以下内容
) V3 }: s2 w1 {6 XEmail | Phone | ID |7 w: H: u, [1 a# Q1 E) D6 U4 h
------------------------|! q- C0 ?3 ^# Z$ D
x@x.com| 555-5555| 001 |
4 T/ D5 F- ^: s5 ?x@x.com| 555-5556| 001 |
& p2 g5 V( L* t G. v. i; p0 x相反是:
7 ?/ M% f4 R' e, lEmail | Phone | Phone2 | Phone3 | ID |4 |$ t) @6 c/ Z: K- a+ {8 f0 K; c
--------------------------------------------| b8 h x% K0 l4 B9 _
x@x.com| 555-5555| 555-5556 | NULL | 001 |
" p( i5 `: ]* e4 F/ k7 w4 f尽管这可能是不好的做法,但严格来说,我可以将结果导出到CSV,以便将该数据上传到CRM系统,该系统在导入时不允许任何重复。从那以后MIN,我四处张望,似乎找不到与电话号码相关的答案,并且MAX只允许输入两个而已。
8 @4 r- _ ?1 ?' |最复杂的部分是数据库的结构,最有可能涉及复杂的查询。这是到目前为止我尝试过的操作,但是只能获得2个数字,并且我需要其余的列(请记住,我不是SQL专家):8 H' P4 J% O) W* U0 A: @2 y8 r
SELECT DISTINCT& a3 ^# a! B# K' ~4 o
C.CONTACTID,
: p6 L* z |3 x8 y MAX(T.NUMBERVALUE) AS Phone1,( Q: }5 i3 I/ } ?5 f" N: N
MIN(T.numbervalue) AS Phone29 h7 T) X# x5 i+ N$ A/ }$ X
FROM TBL_PHONE T" I5 [* N! ]& O4 I/ ~ P. P
JOIN TBL_CONTACT C ON C.CONTACTID = T.CONTACTID
\: y4 I; }; i5 d JOIN TBL_EMAIL E ON E.CONTACTID = C.CONTACTID
( k4 b% S& |' O6 jWHERE T.NUMBERVALUE IS NOT NULL$ Y& r8 D% O' {- T8 m u2 l
AND LEN(T.NUMBERVALUE) > 01 t# M6 g2 f8 \9 \! ?2 b
GROUP BY C.CONTACTID;+ k2 g7 E& q+ ]1 o
% p( N7 ~! M( ^, C [解决方案:
& w% Y1 {& d* \ S. o6 k2 w 9 p9 f) ?9 E; @. M. D& H; g8 w4 n
; ^1 }# J2 O% h8 e
/ @: ~1 B1 P# ` O3 s( E) q 这是一种动态枢轴方法:
& l8 V; k2 i: S) q+ kdeclare @table table (Email varchar(64), Phone varchar(16), ID varchar(3))
9 a3 |+ n% R, R3 ~insert into @table2 [+ T; X6 H6 r/ P
values
3 V. C0 G N6 a7 Q, ~2 z8 y('x@x.com','555-5555','001'),8 l( q5 H9 S# s, e$ V" m
('x@x.com','555-5556','001'),; l4 i3 k2 E3 ^7 H* m: ^
('x@x.com','555-5557','001'),
% \; n* `3 t3 \3 C ?2 x' d('x@x.com','555-5558','001')," L* t$ g( f" B7 e
('x@xdd.com','333-5556','002'),0 K6 U1 l# U! V1 I7 [
('x@xdd.com','444-5556','002'),5 E) y* k( K! Y5 w
('x@xdd.com','777-5556','002')
0 U* x( Y: D0 @0 t N
( J" ]3 G& P# Zselect
1 e5 K7 w; k# m; } Email' F' J- x( ?7 i7 i
,Phone- v5 N9 T2 p) F( R
,ID) w3 C6 u: d* F- |9 b6 d! i; D
,row_number() over (partition by ID order by Phone) as RN
! U% N7 y* _5 K c- ^into #staging+ U1 b1 l! N3 r& r* z
from
/ B( J* F- U5 m% H# t3 @ @table
# ~4 m8 l: o. v5 {6 [, q3 n) Z6 `" T" r7 F' A5 J
0 b2 U- N3 Q$ N
DECLARE @DynamicPivotQuery AS NVARCHAR(MAX)
9 U/ u, Q2 ?, \$ [DECLARE @ColumnName AS NVARCHAR(MAX)
+ w! H' u# P& B) w. H--Get distinct values of the PIVOT Column " `6 N. {/ n( X4 H. G$ E2 b% m, A
SELECT @ColumnName= ISNULL(@ColumnName + ',','')
2 k, x, L6 u% T( n + QUOTENAME(RN)" K7 ^) B% p$ j
FROM (SELECT DISTINCT RN FROM #staging) AS RN
$ ~8 ?) M# S% k- E+ S: G' q, O--Prepare the PIVOT query using the dynamic 3 D; p( E4 E4 I% A/ A! e
SET @DynamicPivotQuery =
$ S- Y8 ]0 `6 ?: i# g N'SELECT Email, ID, ' + @ColumnName + '
9 h* [# L! ]! I4 G: | FROM #staging$ Z+ a% X" p4 y/ P5 q
PIVOT(MAX(Phone) 4 I! g0 E& m# ]5 o" C
FOR RN IN (' + @ColumnName + ')) AS PVTTable'
" C, j, P2 J8 b/ A; D" K--Execute the Dynamic Pivot Query( c* @( A! w% |) e2 c) h# W/ F8 C/ w
EXEC sp_executesql @DynamicPivotQuery
* x$ y" a4 Z2 T8 V9 h7 X6 y/ l1 ldrop table #staging
2 n) N, K! k3 }. a+ K% N# D如前所述,如果您只期望3,则可以跳过动态…
9 J, @" P/ k$ i& A2 f6 _declare @table table (Email varchar(64), Phone varchar(16), ID varchar(3))2 f8 _) b, S/ H p
insert into @table
/ y( j# J" |! V) H* [values) q0 r) `! H$ d$ E' a4 u2 q
('x@x.com','555-5555','001'),
) q! a! u, ~5 _) r( f% h7 f('x@x.com','555-5556','001'),
6 u, Q: O6 k% }& i8 K# s+ k('x@xdd.com','333-5556','002'),0 W ?# [: B/ F& f" p
('x@xdd.com','444-5556','002'),
% v% i! `7 L2 W) C# |- z('x@xdd.com','777-5556','002')7 H Q- d; n' _) N7 q2 w
;with cte as(/ C# W9 A( D$ b9 w3 `
select; M9 _. l# e" G+ C+ I+ A. G# d" c+ E
Email
& e' @! Y" ~4 M5 |& P/ Y ,Phone z' w( y+ D4 \" w3 d
,ID
# a8 z6 }8 ~8 e ,row_number() over (partition by ID order by Phone) as RN# U4 {/ c, T$ B! \
from
$ o, n/ Z9 J6 J @table)' d7 H5 \$ M, k S! o
select
" D. j' ]. H3 ]/ i$ w8 s' }0 v4 b Email: t- a5 p# ?2 A, p
,max(case when RN = 1 then Phone end) as Phone1
, W6 n$ e$ R) K# _ ,max(case when RN = 2 then Phone end) as Phone27 d( B$ [5 ~0 K* p% y5 @
,max(case when RN = 3 then Phone end) as Phone3
& w9 G( i5 ~. _0 G0 c8 A ,ID
* D0 K: C I: _- P o' G; R3 ifrom4 ^( E* @ F$ v+ j* i: @ ^
cte
& O) Q2 [5 a% N/ vgroup by
9 ~- @' @9 T, b( _ Email& L) a$ G# U/ M' J% N* L
,ID |
|