Ayuda con Unpivot SQL2005

Hola Ruben, no existe alguna forma de hacer justo cuando uso el operador
unpivot.

"Ruben Garrigos" wrote:

> 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],