Ayuda con Unpivot SQL2005

02/09/2009 - 19:21 por ronhi | Informe spam
Hola a todos, tengo una super duda con la funcion unpivot de SQL2005.

Resulta que tengo una consulta que normaliza mis registros, todas mis
columnas que dicen Sem las convierto a filas:

select
sistema,
tipo,
producto,
sproducto,
ssproducto,
fcclascar,
rangosemana,
pais,
funcion,
concepto,
Semana,
IntxDev,
CapitalActual,
Monto
From
(select
sistema,
tipo,
producto,
sproducto,
ssproducto,
fcclascar,
rangosemana,
pais,
funcion,
concepto,
IntxDev,
CapitalActual,
[Sem1],
[Sem2],
[Sem3],
[Sem4],
[Sem5],
[Sem6],
[Sem7],
[Sem8],
[Sem9],
[Sem10],
[Sem11],
[Sem12],
[Sem13],
[Sem14],
[Sem15],
[Sem16],
[Sem17],
[Sem18],
[Sem19],
[Sem20],
[Sem21],
[Sem22],
[Sem23],
[Sem24],
[Sem25],
[Sem26],
[Sem27],
[Sem28],
[Sem29],
[Sem30],
[Sem31],
[Sem32],
[Sem33],
[Sem34],
[Sem35],
[Sem36],
[Sem37],
[Sem38],
[Sem39],
[Sem40],
[Sem41],
[Sem42],
[Sem43],
[Sem44],
[Sem45],
[Sem46],
[Sem47],
[Sem48],
[Sem49],
[Sem50],
[Sem51],
[Sem52],
[Sem53],
[Sem54],
[Sem55],
[Sem56],
[Sem57],
[Sem58],
[Sem59],
[Sem60],
[Sem61],
[Sem62],
[Sem63],
[Sem64],
[Sem65],
[Sem66],
[Sem67],
[Sem68],
[Sem69],
[Sem70],
[Sem71],
[Sem72],
[Sem73],
[Sem74],
[Sem75],
[Sem76],
[Sem77],
[Sem78],
[Sem79],
[Sem80],
[Sem81],
[Sem82],
[Sem83],
[Sem84],
[Sem85],
[Sem86],
[Sem87],
[Sem88],
[Sem89],
[Sem90],
[Sem91],
[Sem92],
[Sem93],
[Sem94],
[Sem95],
[Sem96],
[Sem97],
[Sem98],
[Sem99],
[Sem100],
[Sem101],
[Sem102],
[Sem103],
[Sem104]
from Consolidado)
P

Unpivot

(Monto For Semana in
([Sem1],
[Sem2],
[Sem3],
[Sem4],
[Sem5],
[Sem6],
[Sem7],
[Sem8],
[Sem9],
[Sem10],
[Sem11],
[Sem12],
[Sem13],
[Sem14],
[Sem15],
[Sem16],
[Sem17],
[Sem18],
[Sem19],
[Sem20],
[Sem21],
[Sem22],
[Sem23],
[Sem24],
[Sem25],
[Sem26],
[Sem27],
[Sem28],
[Sem29],
[Sem30],
[Sem31],
[Sem32],
[Sem33],
[Sem34],
[Sem35],
[Sem36],
[Sem37],
[Sem38],
[Sem39],
[Sem40],
[Sem41],
[Sem42],
[Sem43],
[Sem44],
[Sem45],
[Sem46],
[Sem47],
[Sem48],
[Sem49],
[Sem50],
[Sem51],
[Sem52],
[Sem53],
[Sem54],
[Sem55],
[Sem56],
[Sem57],
[Sem58],
[Sem59],
[Sem60],
[Sem61],
[Sem62],
[Sem63],
[Sem64],
[Sem65],
[Sem66],
[Sem67],
[Sem68],
[Sem69],
[Sem70],
[Sem71],
[Sem72],
[Sem73],
[Sem74],
[Sem75],
[Sem76],
[Sem77],
[Sem78],
[Sem79],
[Sem80],
[Sem81],
[Sem82],
[Sem83],
[Sem84],
[Sem85],
[Sem86],
[Sem87],
[Sem88],
[Sem89],
[Sem90],
[Sem91],
[Sem92],
[Sem93],
[Sem94],
[Sem95],
[Sem96],
[Sem97],
[Sem98],
[Sem99],
[Sem100],
[Sem101],
[Sem102],
[Sem103],
[Sem104])
) as Unpvt
go

ahora bién, al validar los montos tengo que:

Monto Orginal = 286234640464.9790
Monto Normalizado = 286234640464.8960

Intxdev Original = 10808518385.6499
Intxdev Normalizado = 1124085912107.6000

CapitalActual Orginal = 20527242427.5898
CapitalActual normalizado = 2134833212469.6800

Si se dan cuenta el capital original y el intxdev es muy diferente,
multimplica la cantidad original por 104 y me da una cifra irreal.

alguien sabe que me falta por hacer?
 

Leer las respuestas

#1 Ruben Garrigos
02/09/2009 - 23:48 | Informe spam
Hola ronhi,

¿Podrías añadir también los scripts de definición de la tabla así como algunos
insert con datos de forma que nos sea más fácil determinar el problema que
estás teniendo?

Un saludo,

Rubén Garrigós
Solid Quality Mentors

Blog: http://blogs.solidq.com/es/elrincondeldba

Hola a todos, tengo una super duda con la funcion unpivot de SQL2005.

Resulta que tengo una consulta que normaliza mis registros, todas mis
columnas que dicen Sem las convierto a filas:

select
sistema,
tipo,
producto,
sproducto,
ssproducto,
fcclascar,
rangosemana,
pais,
funcion,
concepto,
Semana,
IntxDev,
CapitalActual,
Monto
From
(select
sistema,
tipo,
producto,
sproducto,
ssproducto,
fcclascar,
rangosemana,
pais,
funcion,
concepto,
IntxDev,
CapitalActual,
[Sem1],
[Sem2],
[Sem3],
[Sem4],
[Sem5],
[Sem6],
[Sem7],
[Sem8],
[Sem9],
[Sem10],
[Sem11],
[Sem12],
[Sem13],
[Sem14],
[Sem15],
[Sem16],
[Sem17],
[Sem18],
[Sem19],
[Sem20],
[Sem21],
[Sem22],
[Sem23],
[Sem24],
[Sem25],
[Sem26],
[Sem27],
[Sem28],
[Sem29],
[Sem30],
[Sem31],
[Sem32],
[Sem33],
[Sem34],
[Sem35],
[Sem36],
[Sem37],
[Sem38],
[Sem39],
[Sem40],
[Sem41],
[Sem42],
[Sem43],
[Sem44],
[Sem45],
[Sem46],
[Sem47],
[Sem48],
[Sem49],
[Sem50],
[Sem51],
[Sem52],
[Sem53],
[Sem54],
[Sem55],
[Sem56],
[Sem57],
[Sem58],
[Sem59],
[Sem60],
[Sem61],
[Sem62],
[Sem63],
[Sem64],
[Sem65],
[Sem66],
[Sem67],
[Sem68],
[Sem69],
[Sem70],
[Sem71],
[Sem72],
[Sem73],
[Sem74],
[Sem75],
[Sem76],
[Sem77],
[Sem78],
[Sem79],
[Sem80],
[Sem81],
[Sem82],
[Sem83],
[Sem84],
[Sem85],
[Sem86],
[Sem87],
[Sem88],
[Sem89],
[Sem90],
[Sem91],
[Sem92],
[Sem93],
[Sem94],
[Sem95],
[Sem96],
[Sem97],
[Sem98],
[Sem99],
[Sem100],
[Sem101],
[Sem102],
[Sem103],
[Sem104]
from Consolidado)
P
Unpivot

(Monto For Semana in
([Sem1],
[Sem2],
[Sem3],
[Sem4],
[Sem5],
[Sem6],
[Sem7],
[Sem8],
[Sem9],
[Sem10],
[Sem11],
[Sem12],
[Sem13],
[Sem14],
[Sem15],
[Sem16],
[Sem17],
[Sem18],
[Sem19],
[Sem20],
[Sem21],
[Sem22],
[Sem23],
[Sem24],
[Sem25],
[Sem26],
[Sem27],
[Sem28],
[Sem29],
[Sem30],
[Sem31],
[Sem32],
[Sem33],
[Sem34],
[Sem35],
[Sem36],
[Sem37],
[Sem38],
[Sem39],
[Sem40],
[Sem41],
[Sem42],
[Sem43],
[Sem44],
[Sem45],
[Sem46],
[Sem47],
[Sem48],
[Sem49],
[Sem50],
[Sem51],
[Sem52],
[Sem53],
[Sem54],
[Sem55],
[Sem56],
[Sem57],
[Sem58],
[Sem59],
[Sem60],
[Sem61],
[Sem62],
[Sem63],
[Sem64],
[Sem65],
[Sem66],
[Sem67],
[Sem68],
[Sem69],
[Sem70],
[Sem71],
[Sem72],
[Sem73],
[Sem74],
[Sem75],
[Sem76],
[Sem77],
[Sem78],
[Sem79],
[Sem80],
[Sem81],
[Sem82],
[Sem83],
[Sem84],
[Sem85],
[Sem86],
[Sem87],
[Sem88],
[Sem89],
[Sem90],
[Sem91],
[Sem92],
[Sem93],
[Sem94],
[Sem95],
[Sem96],
[Sem97],
[Sem98],
[Sem99],
[Sem100],
[Sem101],
[Sem102],
[Sem103],
[Sem104])
) as Unpvt
go
ahora bién, al validar los montos tengo que:

Monto Orginal = 286234640464.9790
Monto Normalizado = 286234640464.8960
Intxdev Original = 10808518385.6499
Intxdev Normalizado = 1124085912107.6000
CapitalActual Orginal = 20527242427.5898
CapitalActual normalizado = 2134833212469.6800
Si se dan cuenta el capital original y el intxdev es muy diferente,
multimplica la cantidad original por 104 y me da una cifra irreal.

alguien sabe que me falta por hacer?

Preguntas similares