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 ALACAKOCAK 8718.22 NULLSUBAT 6129.65 9253.44MART 6308.07 5597.43NISAN 6480.44 6840.27MAYIS 7481.38 6934.04HAZIRAN 6041.96 5331.74TEMMUZ 3876.78 6374.00AGUSTOS NULL NULLEYLÜL NULL NULLEKIM NULL NULLKASIM NULL NULLARALIK 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 BAWHEN '1' THEN SUM(ISNULL(CONVERT(DECIMAL(18,2),TUTAR),0))END AS BORCFROM [LIMON2012].[dbo].[TBLMUHFIS] where HES_KOD='120-01-002'GROUP BY TARIH,BA) AS TABLOPIVOT ( SUM(BORC)FOR [AY] IN ([1],[2],[3],[4],[5],[6],[7], ,[9],[10],[11],[12])) AS PivotTableUNION ALLSELECT * FROM (SELECT MONTH(TARIH) [AY],CASE BAWHEN '2' THEN SUM(ISNULL(CONVERT(DECIMAL(18,2),TUTAR),0))END AS ALACAKFROM [LIMON2012].[dbo].[TBLMUHFIS] where HES_KOD='120-01-002'GROUP BY TARIH,BA) AS TABLOPIVOT(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) |
 |
|
|
|
|