pivot table subtotal

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » SQL Server 2008 » T-SQL (SS2K8) » pivot table subtotal

pivot table subtotal

Rate Topic

Display Mode

Topic Options

Author

Message

sezeren

Posted Wednesday, December 07, 2011 5:12 AM

Forum Newbie

Group: General Forum Members
Last Login: Monday, December 12, 2011 3:45 AM
Points: 1, Visits: 6

hello ,

how subtotal from pivot table
my scrip ;

select
[HESAP KODU],[HESAP ADI],ISNULL(Ocak,0) as Ocak,ISNULL(Subat,0) as Subat,ISNULL(Mart,0)as Mart,ISNULL(Nisan,0)as Nisan,ISNULL(Mayis,0)as Mayis,ISNULL(Haziran,0)as Haziran,ISNULL(Temmuz,0)as Temmuz,ISNULL(Agustos,0)as Agustos,ISNULL(Eylul,0)as Eylul,ISNULL(Ekim,0)as Ekim,ISNULL(Kasim,0)as Kasim,ISNULL(Aral,0)as Aral
from
(SELECT M.CODE AS [HESAP KODU],M.DESCRIPTION AS [HESAP ADI], (ISNULL(ROUND(L.DEBIT-L.CREDIT,2),0)) AS [TOPLAM],
case L.TOTMONTH when 1 then 'Ocak'
when 2 then 'Subat'
when 3 then 'Mart'
when 4 then 'Nisan'
when 5 then 'Mayis'
when 6 then 'Haziran'
when 7 then 'Temmuz'
when 8 then 'Agustos'
when 9 then 'Eylul'
when 10 then 'Ekim'
when 11 then 'Kasim'
when 12 then 'Aral'
end as [AY]

FROM U_001_01_GLACCMONTOTS L WITH(NOLOCK)
JOIN
U_001_GLACCOUNTS M WITH(NOLOCK)ON M.LOGICALREF=L.GLACCOUNTREF
WHERE

(M.CODE LIKE '7%') AND TOTTYPE=1 AND L.ORGUNITREF<>'-1' AND L.TOTMONTH<>'-1' AND L.DEBIT-L.CREDIT<>0 AND L.ORGUNITREF IN(104)

GROUP

BY M.CODE,M.DESCRIPTION,L.DEBIT-L.CREDIT,L.TOTMONTH

)DataTable
PIVOT
(SUM(TOPLAM)
FOR ay
IN ([Ocak],[Subat],[Mart],[Nisan],[Mayis],[Haziran],[Temmuz],[Agustos],[Eylul],[Ekim],[Kasim],[Aral])

)

PivotTable order by 1

Thanks

Post #1217780

drew.allen

Posted Wednesday, December 07, 2011 7:49 AM

Ten Centuries

Group: General Forum Members
Last Login: Thursday, May 02, 2013 1:20 PM
Points: 1,235, Visits: 5,389

PIVOT is probably not the right tool for the job, because it doesn't provide grand totals. The correct tool depends on exactly how you are using this data. If you are simply creating a report, then some sort of reporting software, such as SSRS, is the correct tool.

Drew

J. Drew Allen
Business Intelligence Analyst
Philadelphia, PA

Post #1217919

« Prev Topic | Next Topic »

Permissions