Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

 All Forums
 General SQL Server Forums
 New to SQL Server Programming
 Pivot sum problem

Author  Topic 

Black_Trouble
Starting Member

21 Posts

Posted - 2012-12-04 : 06:14:27
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


[CODE]

/****** 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



[/CODE]

bitsmed
Aged Yak Warrior

545 Posts

Posted - 2012-12-06 : 16:15:54
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
   

- Advertisement -