|
我已经阅读了MS数据透视表上的内容,但在获得正确答案方面仍然遇到问题。
, t) J1 @- {2 S1 ]) B我有一个正在创建的临时表,我们会说第1列是商店编号,第2列是星期编号,最后第3列是某种类型的总数。同样,周号是动态的,商店号是静态的。
3 i* B+ O& l: y o" O4 r/ @Store Week xCount1 q* F! Y5 Y8 E& t$ _
------- ---- ------
+ Z: y% x* p& C7 a7 O6 D$ T l102 1 96/ I" N1 B; m9 K' y8 ~# k: F
101 1 1383 \0 S$ d' L, T
105 1 37
* F, M) ^' p4 H1 V. C109 1 59
n$ i4 J/ O# ^# d% ]: g6 S101 2 282
" ?9 y) H$ Y6 Y6 b/ h5 [# A102 2 212
2 M1 f0 |& e9 t% W105 2 78) m% s( Q! ^) f: s# j" j
109 2 97$ b, v& s( k6 Z/ Z7 `3 U/ p1 J4 j. Q
105 3 60
1 L/ L, \( N2 i# S" ]102 3 123) ~5 R/ Q% `( o/ B. Q0 w3 } D
101 3 220( ~+ R$ l) J* `& f$ T* `
109 3 87 n% ~* ?' S1 p/ W# z
我希望将其作为数据透视表发布,如下所示:, W+ K3 j4 R* |3 S
Store 1 2 3 4 5 6....( p( A5 e& l7 U9 U
-----
9 d9 D9 Q% P C9 d101 138 282 2200 R5 s# i6 ~5 e( I
102 96 212 123 {! X M8 R( r5 y4 g# ]2 X
105 37 . s$ h0 K! R& v, N7 D: y! M' B
109# z8 B8 Y9 K% b: @, v/ v# q
将数字存储在侧面,数周存储在顶部。% t( E! H& q2 p2 D/ C/ ~
6 ^& y+ x1 R: E! y9 T- `: c
解决方案:. g4 P5 f5 A0 F8 Q1 M# ?8 g
% D% c4 I6 O2 f8 K6 A6 F
: e* Y' w' T" ? Q' d+ C( ~
3 Z/ w7 r8 e4 \7 Y1 \ 如果使用的是SQL Server 2005+,则可以使用该PIVOT函数将数据从行转换为列。
8 o+ h& x' |% h) T9 A听起来好像星期几未知,您将需要使用动态sql,但是最初使用硬编码版本更容易看到正确的代码。4 ~$ l2 v- D: e' ?3 W
首先,这里是一些快速的表定义和要使用的数据:
$ H5 o1 \ t |- p; n8 j& k; {1 xCREATE TABLE #yt
% Q; ?8 Q( h9 m+ W% O(
$ d, n- c8 H! } [Store] int, 4 x+ _! I' e1 N) \- c8 Q2 I
[Week] int,
5 k+ a8 H7 }7 G* Q! J" i [xCount] int
, }9 y- \6 i* T);
$ T. l* U- K+ q/ v! X* `INSERT INTO #yt ~3 ?3 |/ L+ k, z/ x
(
/ y- v9 ]. Y' M [Store],
- @# s" M8 J" z6 P! ?# ^- s [Week], [xCount]) O! k6 e- U: R( T7 t
)* f; r4 d: E) h% }3 Z& w
VALUES
) J9 d$ D' {' P (102, 1, 96),* I7 S- N V( O. U3 T5 q/ i
(101, 1, 138),* Y i9 ]1 j; l, i
(105, 1, 37),( ` [: f9 M: r/ q; L! d6 J3 U9 h( G
(109, 1, 59),
& r$ V) r# }* j1 L (101, 2, 282),* `$ C# S* z" w0 ]
(102, 2, 212),
: g( K& M" k. Y" ?0 ?' h (105, 2, 78),
: y$ o) e; y5 Z+ D0 S (109, 2, 97),
$ ?1 P7 _) i8 C (105, 3, 60),8 y, U' ?4 w* a) h$ |, C. G
(102, 3, 123),# D% V0 P! E5 S" }5 ?+ f
(101, 3, 220),
8 \8 u8 K) D0 _$ B' H (109, 3, 87);; x3 M s# q1 Z; f( [
如果您的值已知,那么您将对查询进行硬编码:- K$ R4 P' v) V/ o+ N
select *
* L. W3 P8 [# v- R/ W: qfrom
& R# Z: ^' D# L; V0 _1 m+ d(1 c e0 J w( f6 B. @- j" `
select store, week, xCount
6 ?* H3 d0 X# r. D2 n* U: R from yt
+ n- N( @9 [0 n: {) src
2 v0 K. A8 o7 f5 vpivot
* V1 ?" N+ S1 p- W(
1 e3 E2 S% {5 S5 N sum(xcount)
9 a8 E+ F* t& ?- w+ P* k1 q w0 H7 ~% c for week in ([1], [2], [3])! l3 i% s* U& u4 r+ a
) piv;9 Y1 Q: N. Y1 N: H! v' ~+ k+ p1 j
请参阅SQL演示
+ h3 m; o& I2 X7 z% ?5 i然后,如果您需要动态生成星期数,则代码将是:7 l1 |$ Q7 O8 g3 M( P$ P
DECLARE @cols AS NVARCHAR(MAX),, x; I& G8 g$ T3 z, ` w! r* z
@query AS NVARCHAR(MAX)
; f6 r4 K5 c- [/ h wselect @cols = STUFF((SELECT ',' + QUOTENAME(Week) s# Y; W# u8 N
from yt
- V4 D8 ~& a/ X6 D" B2 o group by Week
1 h0 I _1 y* _; h- g+ X order by Week
! ~ j0 Q- u5 y1 Q FOR XML PATH(''), TYPE
* e; J" p. m/ {: R5 ~$ o ).value('.', 'NVARCHAR(MAX)') 4 e! }! c( x) b b6 C
,1,1,'')
8 c/ o) h3 N' ~7 L/ d* R O! oset @query = 'SELECT store,' + @cols + ' from
$ z$ L/ E. |. G2 { ( j C3 ~- E0 Y1 ^& d
select store, week, xCount$ N2 Y. T. W/ I
from yt
; W. `0 e% c" i+ ]( ~- r ) x
7 X- D) M% z. Q8 `* G$ z) N" { pivot # D$ I7 _3 F6 g4 C* \5 [
(
* C8 \" N" |# Z" q- h sum(xCount)' O5 _5 ~# Q f9 y1 T, r6 y! |0 c& J/ G
for week in (' + @cols + ')
" h8 l5 a2 M! a" d i3 o& a2 S ) p ') o1 I+ D. ]" S
execute(@query);
0 p3 i2 r/ B1 t& ]# O4 @请参阅SQL演示。: A5 k2 ^, @' V* u' {
动态版本会生成week应转换为列的数字列表。两者给出相同的结果:# K; Q) l( P. N7 D
| STORE | 1 | 2 | 3 |0 }4 h6 M) @4 E7 y) P; m; a) U7 a
---------------------------
! j% w4 X) d1 q* t \# i1 U| 101 | 138 | 282 | 220 |/ I& [9 r! n) A) R# m
| 102 | 96 | 212 | 123 |
- ?$ {/ ^( y' T7 B: Q| 105 | 37 | 78 | 60 |
3 N7 r. `$ B) a ?, X1 y' @| 109 | 59 | 97 | 87 | |
|