# Отчет за месяц и год

Я хочу показать таблицу, что месяцы вертикальны, а годы горизонтальны, я ввожу данные года и месяца из раскрывающегося списка. Мой ожидаемый результат ниже:

``2011 2012 2013 2014 jan 1000 1500 5000 1000 feb 00 00 2000 2000 mar . . . . . dec .` `

Мой запрос

` `select datepart(year, DateOfTransaction), left(datepart(month, DateOfTransaction), 3), sum(amount) from TBL_Transactionmaster where datepart(year, DateOfTransaction) = 'input year' and datepart(month, DateOfTransaction) = 'input month'` `

Попробуйте этот запрос.

Для статического стержня

` ` SELECT * FROM ( SELECT left(datename(month,DateOfTransaction),3)as [month], year(DateOfTransaction) as [year] , Amount FROM TBL_Transactionmaster ) as s PIVOT ( SUM(Amount) FOR [Year] in(,,,,) )AS piv` `

Для динамического стержня

` `DECLARE @cols AS NVARCHAR(MAX), @query AS NVARCHAR(MAX) select @cols = STUFF((SELECT distinct ',' + QUOTENAME(year(DateOfTransaction)) from TBL_Transactionmaster FOR XML PATH(''), TYPE ).value('.', 'NVARCHAR(MAX)') ,1,1,'') set @query = 'SELECT [Month],' + @cols + ' from ( SELECT left(datename(month,DateOfTransaction),3)as [month], year(DateOfTransaction) as [year] , Amount FROM TBL_Transactionmaster ) x pivot ( sum(amount) for [year] in (' + @cols + ') ) p ' execute(@query)` `

** За весь месяц и заменить нуль 0 **

` ` SELECT [month], Isnull(,0) as  , ISnull(,0) as  ,ISNULL (,0) as  , ISNULL(,0) as  , ISNULL(,0) as  FROM ( SELECT left(datename(month,DateOfTransaction),3)as [month], Amount, year(DateOfTransaction) as [year] FROM TBL_Transactionmaster UNION ALL select [MONTH], Amount, [year] FROM (Select 'Jan' as [Month] , 0 as Amount, year(Getdate()) as [year] Union ALL Select 'Feb' as [Month] , 0 as Amount, year(Getdate()) as [year] Union ALL Select 'Mar' as [Month] , 0 as Amount, year(Getdate()) as [year] Union ALL Select 'Apr' as [Month] , 0 as Amount, year(Getdate()) as [year] Union ALL Select 'May' as [Month] , 0 as Amount, year(Getdate()) as [year] Union ALL Select 'Jun' as [Month] , 0 as Amount, year(Getdate()) as [year] Union ALL Select 'Jul' as [Month] , 0 as Amount, year(Getdate()) as [year] Union ALL Select 'Aug' as [Month] , 0 as Amount, year(Getdate()) as [year] Union ALL Select 'Sep' as [Month] , 0 as Amount, year(Getdate()) as [year] Union ALL Select 'Oct' as [Month] , 0 as Amount, year(Getdate()) as [year] Union ALL Select 'Nov' as [Month] , 0 as Amount, year(Getdate()) as [year] Union ALL Select 'Dec' as [Month] , 0 as Amount, year(Getdate()) as [year] ) MN ) as s PIVOT ( SUM(Amount) FOR [Year] in(,,,,) )AS piv` `

Вы можете использовать такой запрос:

` `SELECT DATENAME(MONTH, DateOfTransaction) As [Month] , SUM(CASE WHEN DATEPART(YEAR, DateOfTransaction) = 2011 THEN amount ELSE 0 END) AS  , SUM(CASE WHEN DATEPART(YEAR, DateOfTransaction) = 2012 THEN amount ELSE 0 END) AS  , SUM(CASE WHEN DATEPART(YEAR, DateOfTransaction) = 2013 THEN amount ELSE 0 END) AS  , SUM(CASE WHEN DATEPART(YEAR, DateOfTransaction) = 2014 THEN amount ELSE 0 END) AS  FROM TBL_Transactionmaster GROUP BY DATENAME(MONTH, DateOfTransaction), DATEPART(MONTH, DateOfTransaction) ORDER BY DATEPART(MONTH, DateOfTransaction)` `

Вы можете использовать Dynamic SQL следующим образом:

` `DECLARE @sql nvarchar(max); SELECT @sql = ISNULL(@sql, 'DATENAME(MONTH, DateOfTransaction) As [Month]') + ', SUM(CASE WHEN DATEPART(YEAR, DateOfTransaction) = ' + CAST(DATEPART(YEAR, DateOfTransaction) AS VARCHAR(5)) + ' THEN amount ELSE 0 END) AS [' + CAST(DATEPART(YEAR, DateOfTransaction) AS varchar(5)) + ']' FROM TBL_Transactionmaster GROUP BY DATEPART(YEAR, DateOfTransaction) ORDER BY DATEPART(YEAR, DateOfTransaction); SET @sql = 'SELECT ' + @sql + ' FROM TBL_Transactionmaster GROUP BY DATENAME(MONTH, DateOfTransaction), DATEPART(MONTH, DateOfTransaction) ORDER BY DATEPART(MONTH, DateOfTransaction)'; EXEC(@sql);` `
Давайте будем гением компьютера.