This query return:
month 2014 2015
apr null 2000
feb 3000 null
dec null 2000
may 5000 null
How to solve Null value convert into zero and Showing all month by order
This is my query:
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)
Aucun commentaire:
Enregistrer un commentaire