|
我需要使用自定义列名来旋转表。请参阅下面的表格格式。
2 E+ j% s0 _5 s( o4 n当前格式 :* C: K L9 l! G
ID question Answer
! E$ Q2 l# B' S7 f- X4482515 I would like to be informed by mail. No( Y! D% C. A2 w8 G
4482515 Plan to Purchase? Over 12 months; |. c3 O: N. J& J* m- ^2 L% g/ C
4482515 Test Question Text some Answer9 r1 Z! T, V$ E5 B! s0 c1 \
我想以以下格式显示数据。
. y9 R* ~2 D5 M所需格式 :" t1 I7 S) G% Q$ L! V0 h* K8 l
ID question 1 Answer1 question 2 Answer 24 i) m+ x; I- U6 ~5 v& U
4482515 I would like to be informed by mail. NO Plan to Purchase? Over 12 months
# o3 x- N: u* C. L+ r$ i- Z' `请注意:我不知道连续出现的问题和答案的数量,因此应动态生成列的question1 Answer1。
- [* F7 G5 |- f( r) D7 ?谢谢+ y3 c3 ?$ V4 Q3 i
编辑:谢谢您的帮助,我尝试一下您提供给我的动态代码,并得到此错误。2 {& A: u/ G Q/ S- \2 ?
Msg 8167, Level 16, State 1, Line 1
" J; b* k8 s4 |7 |. VThe type of column "answer" conflicts with the type of other columns specified in the UNPIVOT list.. S: ]5 I$ v' J: Y
我的桌子是
% q2 W8 B' t- t- N5 i; yRID Question Answer
5 t! N: w, D, N! `5 O3 N4482515 Some Question1 Some Answer13 a1 G6 R) D" _% V$ Q3 |4 [2 m
4482515 Some Question2 Some Answer2 A; \8 Q$ p' W$ O; Y I5 v
4482515 Some Question3 Some Answer3, }9 l8 h3 a" M) w3 I. W C L
4484094 Some Question1 Answer1
& j+ g( r% S' ?; C% P' D, B. R4484094 Some Question2 Answer2, m0 n! |! m, a0 p* l3 s
4484094 Some Question3 Answer3
! L! l( M' Y# Z" Q8 }$ C4484094 Some Question4 Answer4) k/ m' t. o& t& P. z
我打印出SQL,结果如下。( l, B' E" o, j3 q& Q: G
SELECT rid, [question1],[answer1],[question2],[answer2],[question3],[answer3],[question4],[answer4],[question5],[answer5],[question6],[answer6]6 E3 k/ L0 }1 W5 u; Z
from/ m9 r9 j3 g% u8 a8 G3 v2 D2 U1 h
(5 [' n% N; a* f: s% `
select rid,
+ v- d4 z6 m& @; n9 _5 i col+cast(rn as varchar(10)) col,! i" z# m6 O, s
value
& |% m+ g' U* \' w( k from
" s7 X/ o( C. i2 s; j, m (6 ?3 z# t; X! N+ y1 Y0 I, @
select rid, question, answer,+ P# W5 J; S o$ G' ^! k& ~! h: ]
row_number() over(partition by rid order by rid, question) rn# Z& ^: h% }) u: x
from #tmp_question) a# c+ a/ ]8 ?
) src
( L, @- B5 K( K' ^ unpivot
9 o; V0 Z* Y! T- }1 o! j0 t (! s, J: [# B0 g( K& u+ L
value4 z: X; d! L3 X+ R$ B& }
for col in (question, answer)
; D# k( d2 g/ T0 N ) unpiv
) l, O" v9 J/ n ) d+ w. j0 |1 h- w: I
pivot
7 n1 X% T7 N" d8 f9 h (
& u% y+ {; e- b+ ]0 R max(value)4 c* H) O8 d* K# G
for col in ([question1],[answer1],[question2],[answer2],[question3],[answer3],[question4],[answer4],[question5],[answer5],[question6],[answer6])# a3 x) K6 u& g
) p& D8 J, c* A0 I& ]' g0 B9 P
我的原始SQL代码也在下面
5 N1 z. v! z- g. I1 C DECLARE @cols AS NVARCHAR(MAX),/ x5 p0 R; T. Q# s2 ^
@query AS NVARCHAR(MAX)* x* g$ b/ d- a* L& B" f- a B
select @cols = STUFF((SELECT ',' + QUOTENAME(c.col+cast(rn as varchar(10))) 5 s& x$ C. P/ R3 Q, s; n- o
from 9 o/ A) G. p( A/ U$ c, k' q0 F+ U" _
( W; t! T, W8 I2 ]6 N
select row_number() over(partition by rid
6 r, c. U, \* ?' W6 B% I& r% s# J order by rid, question) rn
- a3 t( M/ E+ m$ {' _1 c- X& { from #tmp_question0 P( p+ C4 y. b
) d
7 N8 a. u' P+ |/ d; T6 Q cross apply" A( L& C+ \8 {" R, C
(4 j% D! S4 ?; Q H! Z8 t) ?5 |2 e
select 'question' col, 1 sort union all select 'answer', 25 g5 N+ w3 {$ `- r1 @0 C% V/ {
) c: `6 x4 f) F8 l) I- R* u
group by col, rn, sort
% P. G' ^+ F/ H( P, n. j order by rn, sort
4 ~- `/ W) y9 _- d) Y FOR XML PATH(''), TYPE% v* D) ?8 X' P7 r5 }& X3 n$ D
).value('.', 'NVARCHAR(MAX)') * S9 M+ Q4 c% |! A3 K
,1,1,'')% C) J* e: ~) Q8 t& V9 |! W
set @query = 'SELECT rid, ' + @cols + '2 y! n/ J/ n" t1 M' ^0 J/ ^
from
g i/ e& X- M( B( ~4 }2 o% G3 \$ M9 @ (! B# d( }1 ^. z" ?/ Z
select rid,, v2 a8 x9 E& z( n
col+cast(rn as varchar(10)) col,
' ^5 d9 b& Z: V' |6 X- m( ?8 w value
1 o* R9 ]+ p* g& Z, J5 ~, [: D from
. ]6 v- P' Q5 v (
% d, a# b3 D) X3 N8 u9 u) } select rid, question, answer,1 h+ h" x' G4 n ~2 {. i: e
row_number() over(partition by rid order by rid, question) rn
4 f' D2 ~1 C8 R6 M! Q from #tmp_question, A L5 d: e2 e' L3 N
) src
4 O3 f. X: e# X- q$ H* W7 ^* O' P unpivot
7 p* ` z7 Q2 I () i2 e8 C: }& i' u1 S' P4 ~
value; O A1 ]. `; i' z9 T: E0 c, D$ x
for col in (question, answer)! j, i3 c, P- ~( s0 q
) unpiv/ I4 r( @; p' a
) d
$ G, M: \) M Y5 q- ~6 p pivot 5 J7 \+ f' d O0 v1 M' Q
(, c- A; F& z6 e. z2 o8 l- W; x
max(value)! T+ D8 N0 Q4 P& E" ]* A: X2 k
for col in (' + @cols + ')
8 w% K# r" R& N) ^. W. P. V ) p ' ^* F1 V5 P) \4 v
--print @query
3 A7 F+ l" x5 }execute(@query);
1 ]# r, \, G/ v: B等待您的帮助!# s( u/ c" {5 N/ P6 J
: H3 \) S0 ^# h4 [$ S3 {
解决方案:& E' B" E1 m5 M. E
0 Z( U* h a7 R* |, G2 ] X. n' e- l. I9 O c( p4 j1 a, G+ A8 o+ ]' b& E2 i
( X: I& f7 T3 z8 d6 | 您可以通过几种方法来执行此操作。
X* \7 E/ h# b7 g# |如果您有已知数量的问题/答案,则可以将其row_number()与聚合函数和CASE表达式一起使用:9 c- p% f* k. A7 N0 B; H6 \; d
select id,
; ]5 j! k) |5 a2 O% I max(case when rn = 1 then question end) question1,) F1 V4 V( Q4 E! h# r
max(case when rn = 1 then answer end) answer1,9 h/ F/ J1 o+ n/ H p6 P, u9 ]
max(case when rn = 2 then question end) question2,0 W) R6 H: J) M# b/ Q
max(case when rn = 2 then answer end) answer2,
) s" E0 w0 O. R0 Y5 ? max(case when rn = 3 then question end) question3,
( e6 i: [$ F3 X- |4 [ max(case when rn = 3 then answer end) answer3/ q& y- j1 Q; Y7 m: j) D
from* g' i& {# a2 v( a9 ]! s4 d
(
8 G8 c- J, H# g9 g select id, question, answer,
6 e- F4 X& p2 I/ \" J row_number() over(partition by id order by id, question) rn- [) H- v* I/ o6 ^& Z3 j
from yt2 S! C% E9 ~ P" L" G8 h
) src, C6 O' R8 }/ r
group by id;
# G/ b( l& r" |3 l- a7 ?参见带有演示的SQL Fiddle
2 O4 `! l2 Q6 {1 O X$ [9 a另一个建议是同时使用UNPIVOT和PIVOT函数来获得结果。UNPIVOT将使用您的question和answer列,并将它们转换为多行。8 N. k. t$ I2 |2 C6 V. c4 Z. @) H5 Q
UNPIVOT的基本语法为:
# @0 p% ?) L3 e6 T$ j: Tselect id,
! @. \* R+ @0 ~; m; |; H1 S col+cast(rn as varchar(10)) col,# [3 m2 g: [$ A) R$ K# l6 L
value
7 Q4 F, {: q. C( Ifrom) {6 v7 Z) y* I i7 C: v
(0 h$ F+ z, u% F% Y+ L
-- when you perform an unpivot the datatypes have to be the same. 7 y3 A p) S( ~4 E: M% L
-- you might have to cast the datatypes in this query
( {$ e$ a% m; e: M* d select id, question, cast(answer as varchar(500)) answer,. y! @' K; T+ t1 y
row_number() over(partition by id order by id, question) rn
. U8 k" d. c' r: x, E/ e from yt5 k, n' c! i, K# X- E- C7 K( O
) src" \; q6 \. B9 X( E
unpivot
$ o- Y* M4 O& w) D' `" i4 ^. m, y(. }, G# D9 T0 A+ R& A _
value
, ^5 H& ^0 q3 d for col in (question, answer), L% y7 c' `+ |- K0 j! h A/ T
) unpiv;
8 X; Y( [; ]. R7 ?5 L参见演示。结果如下:% ^" [# A0 `# g$ ]
| ID | COL | VALUE |; M4 y. q/ n! O5 s I1 f3 t# h
--------------------------------------------------------------3 ^ x3 U4 d, a" _1 |6 V, u; w0 M
| 4482515 | question1 | I would like to be informed by mail. |8 u9 B9 C3 ^9 I
| 4482515 | answer1 | No |" B; M7 l7 D* L
| 4482515 | question2 | Plan to Purchase? |7 f- N0 T5 |/ J1 O- q7 N: i
| 4482515 | answer2 | Over 12 months |
, y2 E3 p- P, N3 a( z8 l8 w| 4482515 | question3 | Test Question Text |$ L1 \4 e5 A) `
| 4482515 | answer3 | some Answer |
9 @+ s5 [# @/ y如您所见,我row_number()在初始子查询中添加了一个值,以便您可以将每个答案与该问题相关联。一旦取消透视,您就可以将结果与question/answer连为一体的行号值在新的列名称上进行透视。具有PIVOT语法的代码将是:
8 V" s- T" b" O# u1 Z7 ^1 |" u! oselect id, question1, answer1, question2, answer2,
, }5 [& u4 v3 O! T @ question3, answer33 @, J7 p& e) D2 D4 ?
from. p, |( s) A6 B
(1 _& W' Y; Q! C* x L4 C
select id,
9 ]. ^: S. B; ^9 v) i* ?' C col+cast(rn as varchar(10)) col,$ S) J5 O1 A4 ^1 _, ^
value% N- Y* c7 V# c% P
from; d, T% @8 A6 l$ L# E: k
(
p8 s. w' S: C' E9 _# u -- when you perform an unpivot the datatypes have to be the same.
* \ r7 L8 F9 i* L0 V. a6 j$ i- ? -- you might have to cast the datatypes in this query
7 K# F$ _8 i% _$ o7 s; K W6 e; i3 ^. s select id, question, cast(answer as varchar(500)) answer,' q- n( {9 V3 |) I* Q5 _* w
row_number() over(partition by id order by id, question) rn
6 ^7 @* [( Z3 J+ X) ]" r from yt4 o( t U' @: \& c* h+ G$ {
) src
# I! H1 }& a, G( d( N) n3 _ unpivot$ r: l( l4 e9 Q6 b, K
(* z' g5 }3 h+ R) Z2 m
value
* \: o+ B, b& S5 o, J* S+ G for col in (question, answer)4 I( P" U! Q2 Y8 o7 W
) unpiv
D& H7 Y2 v: B' q# ^) d( r8 E& y& I- A& u5 n( {: N
pivot
% y3 C7 v- S% A$ t/ u- a; D(/ h1 ?( \) n+ Q L, a6 i; g
max(value): k; t, }- P# |& N S2 B# g1 ~
for col in (question1, answer1, question2, answer2,- w+ w* l' Q- ^" A. V, H1 n
question3, answer3)
i8 B4 A& d( ]6 O; _+ D6 d# {) piv;
5 c, A- h, E1 m) x1 t3 Z7 o; U请参阅带有演示的SQL
8 ~+ v0 y9 `. j+ t' DFiddle。现在,根据您的情况,您表示将有数量众多的问题/答案。如果是这种情况,那么您将需要使用动态SQL来获取结果:
; G9 c8 E& B4 f' X: }+ ODECLARE @cols AS NVARCHAR(MAX),$ W7 ^ M+ W+ ]
@query AS NVARCHAR(MAX)& v" h! t$ N F0 P3 P% A
select @cols = STUFF((SELECT ',' + QUOTENAME(c.col+cast(rn as varchar(10))) 7 a; l$ f7 F2 P* [- i s3 |
from $ L5 V) T/ N' Y6 l) v
(5 a7 z* I5 R" H4 F' ?) `( n
select row_number() over(partition by id
# F" Q( W9 }5 V: e" B! }9 Y9 @% ~ order by id, question) rn
- v- c2 q/ L' s% O0 v from yt( Q* F0 n; I1 L+ k+ `8 e
) d
- t8 K+ k1 W, U cross apply! R* Q) U# t9 ~+ v' w- q
(
s. S& f3 {$ G5 r5 [/ t/ I) { select 'question' col, 1 sort union all select 'answer', 2' C0 U8 E$ {( l" L
) c
9 O2 D3 \. ^; r8 V* b group by col, rn, sort1 q( A ?9 C& Q5 F: Q9 G
order by rn, sort
4 j% [- L0 P% U5 s+ z! } FOR XML PATH(''), TYPE) N& J: g5 c3 a- D* f; d
).value('.', 'NVARCHAR(MAX)')
0 }4 x- [' W3 @7 Q0 `. { ,1,1,'')
& A/ @0 q! E% i# K+ f) K0 |set @query = 'SELECT id, ' + @cols + '* v1 X0 X2 a; J# a& u3 d) i/ o* e
from/ U- r+ t+ I. Z% O! f. X+ S
(5 \+ T4 C* W- [! {
select id,& X0 K* o8 a' e
col+cast(rn as varchar(10)) col,
- [. F6 f' }& ` value, D/ J( z4 r$ c+ s* h. r
from
8 t* ^& b; }8 x+ x$ [ (
: w) e7 j" F) t2 r" } -- when you perform an unpivot the datatypes have to be the same.
8 v1 ]( a! U: F) y+ X: k* K# ^+ } -- you might have to cast the datatypes in this query! g; [7 E1 m' L3 b3 u% S, j. e. P
select id, question, cast(answer as varchar(500)) answer,
8 h# Y+ h h6 ?& p row_number() over(partition by id order by id, question) rn4 k% H1 d9 O9 r- z0 j4 `/ J
from yt+ }: P- X0 U' Q8 K# N# g# u. L
) src$ {" w; C' R9 i8 ^0 _
unpivot
( l1 F- {5 o( E# m5 i# r (+ B+ n$ C; t' V( j% f$ ? w9 U
value/ v& p1 v. H/ C l: b) N
for col in (question, answer)+ m& s, }1 Y1 Y* [0 m% K1 Q
) unpiv/ Y: w8 d$ {- X/ Z# ?7 \: D0 z
) d9 g7 z" H4 T' P _, F. I* P
pivot # q7 U3 @) V0 z9 |8 ^ M/ |
(
) W/ q0 p4 c4 C5 l9 {+ I( U max(value)) n! f7 W# _3 \& g, ]. a
for col in (' + @cols + ')
! v' c8 @5 N" j ) p '
9 d1 `. }7 E) M6 F1 U9 \execute(@query);. g' B) b$ l: D# e" r% A
请参阅带有演示的SQL Fiddle。这些给出了结果:
5 J9 z% L! O6 c4 z* L1 c% @| ID | QUESTION1 | ANSWER1 | QUESTION2 | ANSWER2 | QUESTION3 | ANSWER3 |% C. F5 A7 a/ G
------------------------------------------------------------------------------------------------------------------------------------2 E! R( k& O- X4 Y4 V
| 4482515 | I would like to be informed by mail. | No | Plan to Purchase? | Over 12 months | Test Question Text | some Answer | |
|