Ayuda con Unpivot SQL2005

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?

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?
>

Hola ronhi,

Perdona, pero no entiendo cual es la pregunta...

Un saludo,

Rubén Garrigós
Solid Quality Mentors

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

> Hola y gracias por contestar Ruben, mira te pongo este ejemplo
>
> ID VALOR HUGO JAVIER RAUL
> 1 100 2 1 7
> 2 400 4 1 5
> 3 200 5 3 2
> 4 300 2 2 1
> despues de usar unpivot me resulta esto:
>
> ID VALOR EMPLEADO TOTAL
> 1 100 HUGO 2
> 1 100 JAVIER 1
> 1 100 RAUL 7
> 2 400 HUGO 4
> 2 400 JAVIER 1
> 2 400 RAUL 5
> 3 200 HUGO 5
> 3 200 JAVIER 3
> 3 200 RAUL 2
> 4 300 HUGO 2
> 4 300 JAVIER 2
> 4 300 RAUL 1
> Me puedo percatar que el campo VALOR se repite con cada uno de los ID.
>
> "Ruben Garrigos" wrote:
>
>> 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:

Hola ronhi,

Podrías aplicar un sumatorio filtrando los duplicados por ejemplo con distinct.
Sobre el ejemplo que me pusiste sería algo así:

select SUM (valor) from
(select distinct id,valor from tabla) t

Un saludo,

Rubén Garrigós
Solid Quality Mentors

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

> Perdon Ruben, pasa que cuando intento analizar la información, esta la
> entrego con Intxdev, capitalactual y monto.
>
> El monto es dispersado al momento de normalizar, al sumar las
> cantidades son las mismas, pero no sucede en el INTXDEV ni el
> CAPITALACTUAL.
>
> En el ejemplo que puse al final, si sumas el TOTAL este sumara lo
> mismo al momento de dispersar, pero si sumas el VALOR te daras cuenta
> de que se duplica.
>
> Es eso lo que me gustaria poder controlar ya que al revisar los
> totales todo checa y nada cuadra.
>
> "Ruben Garrigos" wrote:
>
>> Hola ronhi,
>>
>> Perdona, pero no entiendo cual es la pregunta...
>>
>> Un saludo,
>>
>> Rubén Garrigós
>> Solid Quality Mentors
>> Blog: http://blogs.solidq.com/es/elrincondeldba
>>
>>> Hola y gracias por contestar Ruben, mira te pongo este ejemplo
>>>
>>> ID VALOR HUGO JAVIER RAUL
>>> 1 100 2 1 7
>>> 2 400 4 1 5
>>> 3 200 5 3 2
>>> 4 300 2 2 1
>>> despues de usar unpivot me resulta esto:
>>> ID VALOR EMPLEADO TOTAL
>>> 1 100 HUGO 2
>>> 1 100 JAVIER 1
>>> 1 100 RAUL 7
>>> 2 400 HUGO 4
>>> 2 400 JAVIER 1
>>> 2 400 RAUL 5
>>> 3 200 HUGO 5
>>> 3 200 JAVIER 3
>>> 3 200 RAUL 2
>>> 4 300 HUGO 2
>>> 4 300 JAVIER 2
>>> 4 300 RAUL 1
>>> Me puedo percatar que el campo VALOR se repite con cada uno de los
>>> ID.
>>> "Ruben Garrigos" wrote:
>>>
>>>> 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],

Hola ronhi,

Creo que lo que pides nada tiene que ver con el operador unpivot. Si quieres
que te "salgan las cuentas" haciendo eso siempre puedes dividir el valor
de la columna Valor entre el número de columnas que pivotas. De esa forma
al sumar te quedaría igual. Es decir 1000/3+1000/3+1000/3 = 1000. En tu caso
si tienes 104 columnas pues deberías dividir entre 104.

Un saludo,

Rubén Garrigós
Solid Quality Mentors

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

> Hola Ruben buen dia,
>
> Mira el detalle aqui es que la sumatoria del valor ya no seria real al
> valor inicial es decir yo tengo en "valor" 1000 si aplico unpivot el
> total del campo "Valor" seria 3000. Al momento de mostrar la
> informacion a los usuarios esperaria ver 1000 no 3000.
>
> Crees que haya algun modo de controlar eso?
>
> Saludos.
>
> "Ruben Garrigos" wrote:
>
>> Hola ronhi,
>>
>> Podrías aplicar un sumatorio filtrando los duplicados por ejemplo con
>> distinct. Sobre el ejemplo que me pusiste sería algo así:
>>
>> select SUM (valor) from (select distinct id,valor from tabla) t
>>
>> Un saludo,
>>
>> Rubén Garrigós
>> Solid Quality Mentors
>> Blog: http://blogs.solidq.com/es/elrincondeldba
>>
>>> Perdon Ruben, pasa que cuando intento analizar la información, esta
>>> la entrego con Intxdev, capitalactual y monto.
>>>
>>> El monto es dispersado al momento de normalizar, al sumar las
>>> cantidades son las mismas, pero no sucede en el INTXDEV ni el
>>> CAPITALACTUAL.
>>>
>>> En el ejemplo que puse al final, si sumas el TOTAL este sumara lo
>>> mismo al momento de dispersar, pero si sumas el VALOR te daras
>>> cuenta de que se duplica.
>>>
>>> Es eso lo que me gustaria poder controlar ya que al revisar los
>>> totales todo checa y nada cuadra.
>>>
>>> "Ruben Garrigos" wrote:
>>>
>>>> Hola ronhi,
>>>>
>>>> Perdona, pero no entiendo cual es la pregunta...
>>>>
>>>> Un saludo,
>>>>
>>>> Rubén Garrigós
>>>> Solid Quality Mentors
>>>> Blog: http://blogs.solidq.com/es/elrincondeldba
>>>>> Hola y gracias por contestar Ruben, mira te pongo este ejemplo
>>>>>
>>>>> ID VALOR HUGO JAVIER RAUL
>>>>> 1 100 2 1 7
>>>>> 2 400 4 1 5
>>>>> 3 200 5 3 2
>>>>> 4 300 2 2 1
>>>>> despues de usar unpivot me resulta esto:
>>>>> ID VALOR EMPLEADO TOTAL
>>>>> 1 100 HUGO 2
>>>>> 1 100 JAVIER 1
>>>>> 1 100 RAUL 7
>>>>> 2 400 HUGO 4
>>>>> 2 400 JAVIER 1
>>>>> 2 400 RAUL 5
>>>>> 3 200 HUGO 5
>>>>> 3 200 JAVIER 3
>>>>> 3 200 RAUL 2
>>>>> 4 300 HUGO 2
>>>>> 4 300 JAVIER 2
>>>>> 4 300 RAUL 1
>>>>> Me puedo percatar que el campo VALOR se repite con cada uno de los
>>>>> ID.
>>>>> "Ruben Garrigos" wrote:
>>>>>> 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],