sql - 月度和年度明智的报告

Question

我想显示一个表格，月份是垂直的，年份是水平的，我从下拉列表中输入年份和月份。我的预期输出如下:

     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'

Answer 1

尝试这个查询。

对于静态枢轴

 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([2011],[2012],[2013],[2014],[2015])
    )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)

**对于整个月份并将 Null 替换为 0 **

 SELECT [month], Isnull([2011],0) as [2011] , ISnull([2012],0) as [2012] ,ISNULL ([2013],0) as [2013] , ISNULL([2014],0) as [2014] , ISNULL([2015],0) as [2015]
    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([2011],[2012],[2013],[2014],[2015])
    )AS piv