SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 Pivot sum problem
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

Black_Trouble
Starting Member

Turkey
21 Posts

Posted - 12/04/2012 :  06:14:27  Show Profile  Reply with Quote
Hi friends,

I build a table. I like it, but I could not get a result.turns out to be exactly as it appears in the picture side of the table.



I want results like this.

BORC ALACAK
OCAK 8718.22 NULL
SUBAT 6129.65 9253.44
MART 6308.07 5597.43
NISAN 6480.44 6840.27
MAYIS 7481.38 6934.04
HAZIRAN 6041.96 5331.74
TEMMUZ 3876.78 6374.00
AGUSTOS NULL NULL
EYLÜL NULL NULL
EKIM NULL NULL
KASIM NULL NULL
ARALIK NULL NULL




/****** Script for SelectTopNRows command from SSMS  ******/
SELECT [1][Ocak], [2][Subat], [3][Mart], [4][Nisan], [5][Mayis], [6][Haziran], [7][Temmuz],
 [Agustos], [9][Eylül], [10][Ekim], [11][Kasim], [12][Aralik]
 FROM (SELECT MONTH(TARIH) [AY],
CASE BA
WHEN '1' THEN SUM(ISNULL(CONVERT(DECIMAL(18,2),TUTAR),0))
END AS BORC
FROM [LIMON2012].[dbo].[TBLMUHFIS] where HES_KOD='120-01-002'
GROUP BY TARIH,BA
) AS TABLO
PIVOT 
(
 SUM(BORC)
FOR [AY] IN ([1],[2],[3],[4],[5],[6],[7],,[9],[10],[11],[12])) AS PivotTable
UNION ALL
SELECT * FROM 
(SELECT MONTH(TARIH) [AY],
CASE BA
WHEN '2' THEN SUM(ISNULL(CONVERT(DECIMAL(18,2),TUTAR),0))
END AS ALACAK
FROM [LIMON2012].[dbo].[TBLMUHFIS] where HES_KOD='120-01-002'
GROUP BY TARIH,BA) AS TABLO
PIVOT
(SUM(ALACAK)
FOR [AY] IN([1],[2],[3],[4],[5],[6],[7],,[9],[10],[11],[12])) AS PivotTable





bitsmed
Constraint Violating Yak Guru

388 Posts

Posted - 12/06/2012 :  16:15:54  Show Profile  Reply with Quote
As this question hasn't been answered, I'm giving it a try:


select case month(tarih)
          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 'Eylül'
          when 10 then 'Ekim'
          when 11 then 'Kasim'
          else then 'Aralik'
       end as ay
      ,sum(case ba
              when '1' then convert(decimal(18,2),isnull(tutar,0))
              else 0
           end
          ) as borc
      ,sum(case ba
              when '2' then convert(decimal(18,2),isnull(tutar,0))
              else 0
           end
          ) as alacak
  from limon2012.dbo.tblmuhfis
 where hes_kod='120-01-002'
 group by month(tarih)
 order by month(tarih)
Go to Top of Page
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.08 seconds. Powered By: Snitz Forums 2000