Recent PostsRecent Posts Popular TopicsPopular Topics
 Home Search Members Calendar Who's On

 pivot table subtotal Rate Topic Display Mode Topic Options
Author
 Message
 Posted Wednesday, December 7, 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 Aralfrom(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)JOINU_001_GLACCOUNTS M WITH(NOLOCK)ON M.LOGICALREF=L.GLACCOUNTREFWHERE (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 1Thanks
Post #1217780
 Posted Wednesday, December 7, 2011 7:49 AM
 SSCrazy Group: General Forum Members Last Login: Friday, December 2, 2016 3:40 PM Points: 2,379, Visits: 9,419
 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 AllenBusiness Intelligence AnalystPhiladelphia, PAHow 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"][/code]. You can find the IFCode tags on the left when you are writing a post.How to Post Performance Problems
Post #1217919

 Permissions