Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

pivot table subtotal Expand / Collapse
Author
Message
Posted Wednesday, December 7, 2011 5:12 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum 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
Posted Wednesday, December 7, 2011 7:49 AM


SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Yesterday @ 3:10 PM
Points: 1,956, Visits: 8,161
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



How to post data/code on a forum to get the best help.
Make sure that you include code in the appropriate IFCode tags, e.g. [code="sql"]<your code here>[/code]. You can find the IFCode tags on the left when you are writing a post.
How to Post Performance Problems
Post #1217919
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse