Ayuda con PIVOT

23/01/2010 - 18:51 por Gabrielg | Informe spam
Hola,

Con este query
SELECT SBI_001.dbo.JDT1.Account,
DatePart("m",SBI_001.dbo.JDT1.Refdate) AS months,
SUM (SBI_001.dbo.JDT1.SYSDeb - SBI_001.dbo.JDT1.SYSCred)
Balance_SYS
FROM SBI_001.dbo.JDT1
WHERE SBI_001.dbo.JDT1.Account LIKE '1%' and
SBI_001.dbo.JDT1.TransType <> '-3'
AND DatePart("yyyy",SBI_001.dbo.JDT1.Refdate) = '2009'
GROUP BY SBI_001.dbo.JDT1.Account, DatePart
("yyyy",SBI_001.dbo.JDT1.Refdate),
DatePart("m",SBI_001.dbo.JDT1.Refdate)

Obtengo:
Account months Balance_SYS
10003321 9 54000
10007885 7 -133905
10006011 10 79729

Como uso pivot para obtener

Account 1 2 3 4 5
6 7 8 9 10 11 12
10003321
54000
10007885
-133905
10006011
79729
 

Leer las respuestas

#1 Miguel Egea
23/01/2010 - 21:09 | Informe spam
Hola Gabriel, mira esto es un ejemplo , en tu caso no debes hacer el sum,
sino pensar en quitar eso y hacer algo parecido a esto..

use AdventureWorks2008
go
select ShipMethodID , [0],[1],[2],[3],[4],[5], [6],[7],[8],
[9],[10],[11],[12]
from (
select ShipMethodID, MONTH(OrderDate) mes ,TotalDue td from
Sales.SalesOrderHeader
) Tabla
PIVOT (
AVG(td)
FOR mes in ([0],[1],[2],[3],[4],[5], [6],[7],[8], [9],[10],[11],[12]) ) as
pvt



Saludos
Miguel Egea
http://www.portalsql.com


"Gabrielg" escribió en el mensaje de
noticias:
Hola,

Con este query
SELECT SBI_001.dbo.JDT1.Account,
DatePart("m",SBI_001.dbo.JDT1.Refdate) AS months,
SUM (SBI_001.dbo.JDT1.SYSDeb - SBI_001.dbo.JDT1.SYSCred)
Balance_SYS
FROM SBI_001.dbo.JDT1
WHERE SBI_001.dbo.JDT1.Account LIKE '1%' and
SBI_001.dbo.JDT1.TransType <> '-3'
AND DatePart("yyyy",SBI_001.dbo.JDT1.Refdate) = '2009'
GROUP BY SBI_001.dbo.JDT1.Account, DatePart
("yyyy",SBI_001.dbo.JDT1.Refdate),
DatePart("m",SBI_001.dbo.JDT1.Refdate)

Obtengo:
Account months Balance_SYS
10003321 9 54000
10007885 7 -133905
10006011 10 79729

Como uso pivot para obtener

Account 1 2 3 4 5
6 7 8 9 10 11 12
10003321
54000
10007885
-133905
10006011
79729

Preguntas similares