月份和年度报告
月份和年度报告
提问于 2015-06-30 06:07:07
我想显示一个表，月份是垂直的，年份是水平的，我给出了一个从下拉列表中输入的年份和月份。我的预期产出如下：
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'回答 2
Stack Overflow用户
回答已采纳
发布于 2015-06-30 06:45:15
尝试这个查询。
用于静态枢轴的
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)**整个月，用0**取代Null
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 pivStack Overflow用户
发布于 2015-06-30 06:57:26
您可以使用这样的查询：
SELECT DATENAME(MONTH, DateOfTransaction) As [Month]
, SUM(CASE WHEN DATEPART(YEAR, DateOfTransaction) = 2011 THEN amount ELSE 0 END) AS [2011]
, SUM(CASE WHEN DATEPART(YEAR, DateOfTransaction) = 2012 THEN amount ELSE 0 END) AS [2012]
, SUM(CASE WHEN DATEPART(YEAR, DateOfTransaction) = 2013 THEN amount ELSE 0 END) AS [2013]
, SUM(CASE WHEN DATEPART(YEAR, DateOfTransaction) = 2014 THEN amount ELSE 0 END) AS [2014]
FROM TBL_Transactionmaster
GROUP BY DATENAME(MONTH, DateOfTransaction), DATEPART(MONTH, DateOfTransaction)
ORDER BY DATEPART(MONTH, DateOfTransaction)您可以像这样使用动态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);页面原文内容由Stack Overflow提供。腾讯云小微IT领域专用引擎提供翻译支持
原文链接：
https://stackoverflow.com/questions/31130773
复制相关文章
相似问题
腾讯云开发者
