MS SQL Server OLAP - Transpose data for cube

Author

Transpose data for cube

Des Norton

2005-11-21, 7:23 am

Hi NG

Please bear with me as I am a total newbie at this.

I need to access data that is stored in a seriously normalised database
structure. The structure is great in that the number of fields in a record
can grow indefinately without any changes to the database structure.

However, reporting on this data is proving to be a problem for the following
reasons:
1. There is no fixed number of fields per record.
2. The record is transposed into multiple records.

A script with a simplified table structure and some sample data are
attached.

The database is MSSQL 2000. I need to use Reporting Svcs 2000 to report on
the data. However, the sheer volume of the data (±12MIL Records) means that
the data needs to be pre-aggregated. I am hoping to use OLAP 2000 to
generate a cube, which I can use as the datasource for my reports.

I hope I'm wrong, but essentially I believe that I need to get the data into
this format:
ClientID RecordGUID ObjectName Code Name Price Qty Supplier
1 5D9081A0-32F1-4EC1-96EF-577D2452FD0B Supplier S1 Supplier 1
1 B5E21569-FD93-4F12-BD75-C481EF0BFAF0 Supplier S2 Supplier 2
1 CAB95242-FEE5-4D81-B88B-4AF5F214354F Product P1 Product 1 50.00 10
5D9081A0-32F1-4EC1-96EF-577D2452FD0B
1 CC8F3D3A-A6F3-48F6-86AF-556441E03809 Product P2 Product 2 80.00 5
B5E21569-FD93-4F12-BD75-C481EF0BFAF0

I can achieve this by writing an external app to extract the data from the
current format and transpose it into predefined tables, which I can then use
as a fact table for my cube. However, I would like to be able to do it all
directly within OLAP.

Thanks in advance
Des Norton

begin 666 DemoData.sql
M0U)%051%(%1!0DQ%(%M
D8F]=+EMD871A3V)J96-T72 H#0H)6U)E8V]R9$=5
M241=(%MU;FEQ=65I9&5N=&EF:65R72!.3U0@3E5,3"!#3TY35%)!24Y4(%M0
M2U]D871A3V)J96-T72!04DE-05)9($M%62 @0TQ54U1%4D5$($-/3E-44D%)
M3E0@6T1&7V1A=&%/8FIE8W1?1U5)1%T@1$5&055,5" H;F5W:60H*2D@+ T*
M"5M/8FIE8W1.86UE72!;=F%R8VAA<ET@*#$P,"D@3D]4($Y53$P@0T].4U12
M04E.5"!;25A?9&%T84]B:F5C=%].86UE72!53DE1544@($Y/3D-,55-415)%
M1" -" BD@3TX@6U!224U!4EE=#
0I'3PT*#0I#4D5!5$4@5
$%"3$4@6V1B;UTN
M6V1A=&%&:65L9%T@* T*"5M296-O<F1'54E$72!;=6YI<75E:61E;G1I9FEE
M<ET@3D]4($Y53$P@0T].4U1204E.5"!;4$M?9&%T849I96QD72!04DE-05)9
M($M%62 @0TQ54U1%4D5$(" !#3TY35%)!24Y4(%M$1E
]D871A1FEE;&1?1U5)
M1%T@1$5&055,5" H;F5W:60H*2D@+ T*"5M& :65L9$YA;65=(%MV87)C
:&%R
M72 H,3 P*2!.3U0@3E5,3" !#3TY35%)!24Y4(%M)6%
]D871A1FEE;&1?3F%M
M95T@54Y)455%("!.3TY#3%535$52140@+ T**2!/3B!;4%))34%265T-"D=/
M#0H-"D-214%412!404),12!;9&)O72Y;9&%T85)E8V]R9%T@* T*"5M296-O
M<F1'54E$72!;=6YI<75E:61E;G1I9FEE<ET@3D]4($Y53$P@0T].4U1204E.
M5"!;4$M?9& %T85)E8V]R9%T@4%))34
%262!+15D@($-,55-415)%1"!#3TY3
M5%)!24Y4(%M$1E]D871
A4F5C;W)D7T=5241=($1
%1D%53%0@*&YE=VED*"DI
M("P-" @E;0VQI96YT241=(%MI;
G1=($Y/5"!.54Q,("P-"@E;3V)J96-T1U5)
M1%T@6W5N:7%U96ED96Y
T:69I97)=($Y/5"!.54Q,("P-"BD@3TX@6U!224U!
M4EE=#0I'3PT*#0I#4D5
!5$4@5$%"3$4@6V1B;UTN6V1A=&%296-O<F1&:65L
M9%T@* T*"5M296-O<F1'54E$72!;=6YI<75E:61E;G1I9FEE<ET@3D]4($Y5
M3$P@0T].4U1204E.5"!;4$M?9& %T85)E8V]R9$9I96QD72
!04DE-05)9($M%
M62 @3D].0TQ54U1%4D5$(" !#3TY35%)!24Y4(%M$1E
]D871A4F5C;W)D1FEE
M;&1?1U5)1%T@1$5&055,5" H;F5W:60H*2D@+ T*"5MO4F5C;W)D1U5)1%T@
M6W5N:7%U96ED96YT:69
I97)=($Y/5"!.54Q,("P-"@E;1FEE;&1'54E$72!;
M=6YI<75E:61E;G1I9FEE<ET@3D]4($Y53$P@+ T*"5M686QU95T@6W9A<F-H
M87)=("@W,# P*2!.3U0@3E5,3" -" BD@3TX@6U!224U!4EE=#
0I'3PT*04Q4
M15(@5$%"3$4@6V1B;UTN6V1A=&%296-O<F1&:65L9%T@5TE42"!.3T-(14-+
M($%$1" -" @E#3TY35%)!24Y4(%M)6
%]D871A4F5C;W)D1FEE;
&1?1&%T85T@
M54Y)455%(" !#3%535$52140@*%MO4F
5C;W)D1U5)1%TL(%M&:65L9$=5241=
M*2 -"D=/#0H-"D-214%412 @24Y$15@@6TE87V1A=&%296-O<F1?0VQI96YT
M241=($]. (%MD8F]=+EMD871A4F5C
;W)D72A;0VQI96YT241=
*2!/3B!;4%))
M34%265T-"D-214%412 @24Y$15@@6TE87V1A=&%296-O<F1?3V)J96-T1U5)
M1%T@3TX@6V1B;UTN6V1
A=&%296-O<F1=*%M/ 8FIE8W1'54E$72D@3TX@
6U!2
M24U!4EE=#0I'3PT*#0I
!3%1%4B!404),12!;9&)O72Y;9&%T85)E8V]R9%T@
M041$( T*"4-/3E-44D%)3E0@6T9+7V1A=&%296-O<F1?9&%T84]B:F5C=%T@
M1D]214E'3B!+15D@*%M
/8FIE8W1'54E$72D@4D5&15)%3D-%4R!;9&)O72Y;
M9& %T84]B:F5C=%T@*%M296
-O<F1'54E$72D@3D]4($9/4B!215!,24-!5$E/
M3B -"D=/#0IA;'1E<B!T86)L92!;9&)O72Y;9&%T85)E8V]R9%T@;F]C:&5C
M:R!C;VYS=')A:6YT(%M
& 2U]D871A4F5C;W)D7V1A
=&%/8FIE8W1=#0I'3PT*
M#0I!3%1%4B!404),12!
;9&)O72Y;9& %T85)E8V]R9$9I96QD72
!!1$0@#0H)
M0T].4U1204E.5"!;1DM?9& %T85)E8V]R9$9I96QD7V
1A=&%&:65L9%T@1D]2
M14E'3B!+15D@*%M& :65L9$=5241=*2!2149%
4D5. 0T53(%MD8F]=+EMD871A

M1FEE;&1=(" A;4F5C;W)D1U5)1%TI($
Y/5"!& 3U(@4D503$E#051)3TX@
+ T*
M"4-/3E-44D%)3E0@6T9+7V1A=&%296-O<F1& :65L9%]D871A4F5C;W)D
72!&
M3U)%24=.($M%62 H6V]296-O<F1'54E$72D@4D5&15)%3D-%4R!;9&)O72Y;
M9& %T85)E8V]R9%T@*%M296
-O<F1'54E$72D@3D]4($9/4B!215!,24-!5$E/
M3B -"D=/#0IA;'1E<B!T86)L92!;9&)O72Y;9& %T85)E8V]R9$9I96QD72
!N
M;V-H96-K(&-O;G-T<F%I;G0@6T9+7V1A=&%296-O<F1&:65L9%]D871A1FEE
M;&1=#0IA;'1E<B!T86)L92!;9&)O72Y;9& %T85)E8V]R9$9I96QD72
!N;V-H
M96-K(&-O;G-T<F%I;G0@6T9+7V1A=&%296-O<F1& :65L9%]D871A4F5C;W)D

M70T*1T\-"@T*#0H-"FEN<V5R="!;9&)O72Y;9&%T84]B:F5C=%T@*%M/8FIE
M8W1.86UE72D@=F%L=65S("@G4')O9'5C="<I( T*:6YS97)T(%MD8F]=+E
MD
M871A3V)J96-T72 H6T]B:F5C=$YA;65=*2!
V86QU97,@*"=3=7!P;&EE<B<I
M( T*#0II;G-E<G0@6V1B;UTN6V1A=&%&:65L9%T@*%M&:65L9$YA;65=*2!V
M86QU97,@*"=#;V1E)RD@#0II;G-E<G0@6V1B;UTN6V1A=&%&:65L9%T@*%M&
M:65L9$YA;65=*2!V86Q
U97,@*"=.86UE)RD@#0II;G-E<G0@6V1B;UTN6V1A
M=&%&:65L9%T@*%M& :65L9$YA;65=*2!V86QU
97,@*"=0<FEC92<I( T*:6YS
M97)T(%MD8F]=+EMD871
A1FEE;&1=("A;1FEE;&1.86UE72D@=F%L=65S("@G
M471Y)RD@#0II;G-E<G0@6V1B;UTN6V1A=&%&:65L9%T@*%M&:65L9$YA;65=
M*2!V86QU97,@*"=3=7!P;&EE<B<I( T*#0H-"@T*9&5C;&%R92! 1U5)1"!U
M;FEQ=65I9&5N=&EF:65R+"! 9D=5240@=6YI<75E:61E;G1I9FEE<B -"@T*
M< V5T($!'54E$(#T@;F5W:
60H*2 -"FEN<V5R="!;9&)O72Y;9&%T85)E8V]R
M9%T@*%M296-O<F1'54E$72P@6T- L:65N=$E$72P@6T]B:F5
C=$=5241=*2!S
M96QE8W0@0$=5240L(#$
L(%M296-O<F1'54E$72!F<F]M(%MD8F]=+EMD871A
M3V)J96-T72!W:&5R92!;3V)J96-T3F%M95T])U-U<'!L:65R)R -"FEN<V5R
M="!;9&)O72Y;9& %T85)E8V]R9$9I96QD72
H6V]296-O<F1'54E$72P@6T9I
M96QD1U5)1%TL(%M686Q
U95TI(" !S96QE8W0@0$=5240L(%
M296-O<F1'54E$
M72P@)U,Q)R!F< F]M(%MD8F]=+EMD871A1
FEE;&1=('=H97)E(%M&:65L9$YA
M;65=/2=#;V1E)R -"FEN<V5R="!;9&)O72Y;9& %T85)E8V]R9$9I96QD72
H
M6V]296-O< F1'54E$72P@6T9I96QD1
U5)1%TL(%M686QU95TI(
"!S96QE8W0@
M0$=5240L(%M296-O<F1'54E$72P@)U-U<'!L:65R(#$G(&9R;VT@6V1B;UTN
M6V1A=&%&:65L9%T@=VAE< F4@6T9I96QD3F%M95T])
TYA;64G( T*#0IS970@
M0$=5240@/2!N97=I9"@I( T*:6YS97)T(%MD8F]=+E
MD871A4F5C;W)D72 H
M6U)E8V]R9$=5241=+"!;0VQI96YT241=+"!;3V)J96-T1U5)1%TI('-E;&5C
M="! 1U5)1" P@,2P@6U)E8V]R9$=524
1=(&9R;VT@6V1B;UTN6V1A=&%/8FIE
M8W1=('=H97)E(%M/8FIE8W1.86UE73TG4W5P<&QI97(G( T*:6YS97)T(%MD
M8F]=+EMD871A4F5C;W)
D1FEE;&1=("A;;U)E8V]R9$=5241=+"!;1FEE;&1'
M54E$72P@6U9A;'5E72D
@('-E;&5C="! 1U5)1"P@6U)E8V]R9$=5241=+" G
M4S(G(&9R;VT@6V1B;UTN6V1A=&%&:65L9%T@=VAE<F4@6T9I96QD3F%M95T]
M)T-O9&4G(" -"FEN<V5R="!;9&)O72Y;9& %T85)E8V]R9$9I96QD72
H6V]2
M96-O< F1'54E$72P@6T9I96QD1
U5)1%TL(%M686QU95TI(
"!S96QE8W0@0$=5
M240L(%M296-O<F1'54E$72P@)U-U<'!L:65R(#(G(&9R;VT@6V1B;UTN6V1A
M=&%&:65L9%T@=VAE< F4@6T9I96QD3F%M95T])
TYA;64G( T*#0H-"G-E="!
M1U5)1" ](&YE=VED*"D@#0II;G-E<G0@6V1B;UTN6V1A=&%296-O<F1=("A;
M4F5C;W)D1U5)1%TL(%M
#;&EE;G1)1%TL(%M/8FIE8W1'54E$72D@<V5L96-T
M($!'54E$+" Q+" !;4F5C;W)D1U5)1%T@9G
)O;2!;9&)O72Y;9&%T84]B:F5C
M=%T@=VAE<F4@6T]B:F5C=$YA;65=/2=0<F]D=6-T)R -"FEN<V5R="!;9&)O
M72Y;9& %T85)E8V]R9$9I96QD72
H6V]296-O< F1'54E$72P@6T9I96QD1
U5)
M1%TL(%M686QU95TI(" !S96QE8W0@0$=5240L(%
M296-O<F1'54E$72P@)U Q
M)R!F< F]M(%MD8F]=+EMD871A1
FEE;&1=('=H97)E(%M&:65L9$YA;65=/2=#
M;V1E)R @#0II;G-E<G0@6V1B;UTN6V1A=&%296-O<F1&:65L9%T@*%MO4F5C
M;W)D1U5)1%TL(%M&:65L9$=5241=+"!;5F%L=65=*2 @<V5L96-T($!'54E$
M+"!;4F5C;W)D1U5)1%TL("=0<F]D=6-T(#$G(&9R;VT@6V1B;UTN6V1A=&%&
M:65L9%T@=VAE< F4@6T9I96QD3F%M95T])
TYA;64G(" -"FEN<V5R="!;9&)O
M72Y;9& %T85)E8V]R9$9I96QD72
H6V]296-O< F1'54E$72P@6T9I96QD1
U5)
M1%TL(%M686QU95TI(" !S96QE8W0@0$=5240L(%
M296-O<F1'54E$72P@)S4P
M+C P)R!F< F]M(%MD8F]=+EMD871A1
FEE;&1=('=H97)E(%M&:65L9$YA;65=
M/2=0<FEC92<@( T*:6YS97)T(%MD8F]=+E
MD871A4F5C;W)D1FEE;&1=("A;
M;U)E8V]R9$=5241=+"!;1FEE;& 1'54E$72P@6U9A;'5E72
D@('-E;&5C="!
M1U5)1"P@6U)E8V]R9$=5241=+" G,3 G(&9R;VT@6V1B;UTN6V1A=&%&:65L
M9%T@=VAE< F4@6T9I96QD3F%M95T])
U%T>2<@( T*<V5T($!F1U5)1" ]("AS
M96QE8W0@6U)E8V]R9$=
5241=(&9R;VT@6V1B;UTN6V1A=&%&:65L9%T@=VAE
M< F4@6T9I96QD3F%M95T])
U-U<'!L:65R)RD@#0II;G-E<G0@6V1B;UTN6V1A
M=&%296-O<F1& :65L9%T@*%MO4F5C;W)D
1U5)1%TL(%M&:65L9$=5241=+"!;
M5F%L=65=*2 @<V5L96-T($!'54E$+"! 9D=5240L(')F+EMO4F5C
;W)D1U5)
M1%T@#0H)"0D)"0D)"0D)"0D)"0D)"0D)" 69R;VT@6V1B;UTN6V1A=
&%/8FIE
M8W1=(&\@#0H)"0D)"0D)"0D)"0D)"0D)"0D)"6EN;F5R(&IO:6X@6V1B;UTN
M6V1A=&%296-O< F1=('(@;VX@;RY;4F5C;
W)D1U5)1%T]<BY;3V)J96-T1U5)
M1%T@#0H)"0D)"0D)"0D)"0D)"0D)"0D)"6EN;F5R(&IO:6X@6V1B;UTN6V1A
M=&%296-O<F1&:65L9%T@<F8@;VX@<BY;4F5C;W)D1U5)1%T]<F8N6V]296-O
M<F1'54E$72 -"@D)"0D)"0D)"0D)"0D)"0D)" 0D):6YN97(@:F]I;B!;9
&)O
M72Y;9&%T849I96QD72!F(&]N(&8N6U)E8V]R9$=5241=/7)F+EM&:65L9$=5
M241=( T*"0D)"0D)"0D)"0D)"0D)"0D)"0EW:&5R92!O+EM/8FIE8W1.86UE
M73TG4W5P<&QI97(G(&%N9"!;1FEE;&1.86UE73TG0V]D92<@86YD(%M686QU
M95T])U,Q)R -"@T*< V5T($!'54E$(#T@;F5W:
60H*2 -"FEN<V5R="!;9&)O
M72Y;9& %T85)E8V]R9%T@*%M296
-O<F1'54E$72P@6T-L:65N=$E$72P@6T]B
M:F5C=$=5241=*2!S96Q
E8W0@0$=5240L(#$L(%M
296-O<F1'54E$72!F<F]M
M(%MD8F]=+EMD871A3V)
J96-T72!W:&5R92!;3V)J96-T3F%M95T])U!R;V1U
M8W0G( T*:6YS97)T(%MD8F]=+E
MD871A4F5C;W)D1FEE;&1=("A;;U)E8V]R
M9$=5241=+"!;1FEE;& 1'54E$72P@6U9A;'5E72
D@('-E;&5C="! 1U5)1"P@
M6U)E8V]R9$=5241=+" G4#(G(&9R;VT@6V1B;UTN6V1A=&%&:65L9%T@=VAE
M< F4@6T9I96QD3F%M95T])
T-O9&4G(" -"FEN<V5R="!;9&)O72Y;9&%T85)E
M8V]R9$9I96QD72 H6V]296-O< F1'54E$72P@6T9I96QD1
U5)1%TL(%M686QU
M95TI(" !S96QE8W0@0$=5240L(%
M296-O< F1'54E$72P@)U!R;V1U8
W0@,B<@
M9G)O;2!;9&)O72Y;9&%T849I96QD72!W:&5R92!;1FEE;&1.86UE73TG3F%M
M92<@( T*:6YS97)T(%MD8F]=+E
MD871A4F5C;W)D1FEE;&1=("A;;U)E8V]R
M9$=5241=+"!;1FEE;& 1'54E$72P@6U9A;'5E72
D@('-E;&5C="! 1U5)1"P@
M6U)E8V]R9$=5241=+" G.# N,# G(&9R;VT@6V1B;UTN6V1A=&%&:65L9%T@
M=VAE< F4@6T9I96QD3F%M95T])
U!R:6-E)R @#0II;G-E<G0@6V1B;UTN6V1A
M=&%296-O<F1& :65L9%T@*%MO4F5C;W)D
1U5)1%TL(%M&:65L9$=5241=+"!;
M5F%L=65=*2 @<V5L96-T($!'54E$+"!;4F5C;W)D1U5)1%TL("<U)R!F<F]M
M(%MD8F]=+EMD871A1FE
E;&1=('=H97)E(%M&:65L9$YA;65=/2=1='DG(" -
M"G-E="! 9D=5240@/2 H<V5L96-T(%M296-O<F1'54E$72!F<F]M(%MD8F]=
M+EMD871A1FEE;&1=('=H97)E(%M&:65L9$YA;65=/2=3=7!P;&EE<B<I( T*
M:6YS97)T(%MD8F]=+EM
D871A4F5C;W)D1FEE;&1=("A;;U)E8V]R9$=5241=
M+"!;1FEE;& 1'54E$72P@6U9A;'5E72
D@('-E;&5C="! 1U5)1"P@0&9'54E$
M+" !R9BY;;U)E8V]R9$=524
1=( T*"0D)"0D)"0D)"0D)"0D)"0D)"0EF<F]M
M(%MD8F]=+EMD871A3V)
J96-T72!O( T*"0D)"0D)"0D)"0D)"0D)"0D)"0EI
M;FYE< B!J;VEN(%MD8F]=+EMD8
71A4F5C;W)D72!R(&]N(&\N6U)E8V]R9$=5
M241=/ 7(N6T]B:F5C=$=5241=(
T*"0D)"0D)"0D)"0D)"0D)"0D)"0EI;FYE
M< B!J;VEN(%MD8F]=+EMD8
71A4F5C;W)D1FEE;&1=(')F(&]N('(N6U)E8V]R
M9$=5241=/ 7)F+EMO4F5C;W)D1U5)1
%T@#0H)"0D)"0D)"0D)"0D)"0D)"0D)
M"6EN;F5R(&IO:6X@6V1B;UTN6V1A=&%& :65L9%T@9B!O;B!F+EM2
96-O<F1'
M54E$73UR9BY;1FEE;&1'54E$72 -"@D)"0D)"0D)"0D)"0D)"0D)"0D)=VAE
M<F4@;RY;3V)J96-T3F%M95T])U-U< '!L:65R)R!A;F0@6T9I9
6QD3F%M95T]
M)T-O9&4G(&%N9"!;5F%L=65=/2=3,B<@#0H-"@T*<V5L96-T('(N6T-L:65N
M=$E$72P@< BY;4F5C;W)D1U5)1%TL(
&\N6T]B:F5C=$YA;65=+"!F+EM&:65L
M9$YA;65=+"!R9BY;5F%L=65=( T*9G)O;2!;9&)O72Y;9&%T84]B:F5C=%T@
M;R -"FEN;F5R(&IO:6X@6V1B;UTN6V1A=&%296-O<F1=('(@;VX@;RY;4F5C
M;W)D1U5)1%T]<BY;3V)J96-T1U5)1%T@#0II;FYE<B!J;VEN(%MD8F]=+EMD
M871A4F5C;W)D1FEE;&1=(')F(& ]N('(N6U)E8V]R9$=524
1=/7)F+EMO4F5C
M;W)D1U5)1%T@#0II;FY
E< B!J;VEN(%MD8F]=+EMD8
71A1FEE;&1=(&8@;VX@
M9BY;4F5C;W)D1U5)1%T
]< F8N6T9I96QD1U5)1%T@#
0IO<F1E<B!B>2!O+EM/
M8FIE8W1.86UE72!D97-C+"!R+EM296-O< F1'54E$72P@9BY;1FEE;
&1.86UE
&72 -"@T*
`
end

Deepak Puri

2005-11-22, 3:24 am

Hi Des,

If both relational SQL Server 2005 and AS 2005 are an option, then maybe
you can create a named query for the fact table in the AS 2005 Data
Source View, which uses the new TSQL PIVOT command to transpose the
data:

http://msdn2.microsoft.com/en-us/library/ms177410.aspx[color=darkred]
Using PIVOT and UNPIVOT

You can use the PIVOT and UNPIVOT relational operators to manipulate a
table-valued expression into another table. PIVOT rotates a table-valued
expression by turning the unique values from one column in the
expression into multiple columns in the output, and performs
aggregations where necessary on any remaining column values that are
desired in the final output.
...[color=darkred]

- Deepak

Deepak Puri
Microsoft MVP - SQL Server

*** Sent via Developersdex http://www.droptable.com ***

Des Norton

2005-11-22, 3:24 am

Hi Deepak

Thanks for the response..

Unfortunately I dont have any control over the box, and am bound to using
SQL2000 and AS2000.

Regards
Des Norton

Darren Gosbell

2005-11-22, 8:24 pm

You can still do Pivots in SQL 2k, it just involves a lot more code than
the PIVOT command.

One such example using dynamic SQL can be found here
http://www.sqlteam.com/item.asp?ItemID=2955, in fact this code is more
flexible than the PIVOT command as it will dynamically work out the
columns, PIVOT in SQL2k5 requires a static list of values.

I'm sure a search on google could turn up many more. I know there are
methods using case statements which require a static column list, but do
not use dynamic sql.

--
Regards
Darren Gosbell [MCSD]
Blog: http://www.geekswithblogs.net/darrengosbell

In article <ObpATIz7FHA.2676@TK2MSFTNGP15.phx.gbl>,
desREMOVEnortonUPPER
CASE@gmail.com says...
> Hi Deepak
>
>
> Thanks for the response..
>
> Unfortunately I dont have any control over the box, and am bound to using
> SQL2000 and AS2000.
>
>
> Regards
> Des Norton
>
>
>

Des Norton

2005-11-28, 7:24 am

Thanks Darren

> http://www.sqlteam.com/item.asp?ItemID=2955,

With some mods for specific requirements, workes like a charm.

Regards
Des Norton

"Darren Gosbell" <jam@newsgroups.nospam> wrote in message
news:MPG. 1dee6ffcf4ca44d49897
eb@news.microsoft.com...
> You can still do Pivots in SQL 2k, it just involves a lot more code than
> the PIVOT command.
>
> One such example using dynamic SQL can be found here
> http://www.sqlteam.com/item.asp?ItemID=2955, in fact this code is more
> flexible than the PIVOT command as it will dynamically work out the
> columns, PIVOT in SQL2k5 requires a static list of values.
>
> I'm sure a search on google could turn up many more. I know there are
> methods using case statements which require a static column list, but do
> not use dynamic sql.
>
> --
> Regards
> Darren Gosbell [MCSD]
> Blog: http://www.geekswithblogs.net/darrengosbell
>
>
>
> In article <ObpATIz7FHA.2676@TK2MSFTNGP15.phx.gbl>,
> desREMOVEnortonUPPER
CASE@gmail.com says...
>

Sponsored Links