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) |
|
|
|
|
|