Check this:
DECLARE @fiscalYearStart DATE = CONVERT(DATE, '2017-04-01')
DECLARE @fiscalYearEnd DATE = DATEADD(MM, 12, @fiscalYearStart)
DECLARE @cols NVARCHAR(2000) = N'';
;WITH Dates AS
(
SELECT @fiscalYearStart AS CommonDate
UNION ALL
SELECT DATEADD(MM, 1, CommonDate) AS CommonDate
FROM Dates
WHERE DATEADD(MM, 1, CommonDate)<@fiscalYearEnd
)
SELECT @cols = STUFF((SELECT '],[' + CONVERT(NVARCHAR(10),CommonDate)
FROM Dates
FOR XML PATH('')), 1, 2, '') + ']'
DECLARE @qry NVARCHAR(MAX) = N'SELECT ' + @cols +
' FROM (' +
' SELECT j.[Value], CAST(DATEADD(DAY,-DAY(j.[Date])+1, CAST(j.[Date] AS DATE)) AS DATE) AS CommonMonthDate' +
' FROM FROM dbo.Reestr AS r LEFT JOIN dbo.Source AS j ON r.Sender_Id=j.ID' +
' WHERE r.Op_Type_Id IN(1, 3, 5)' +
') AS DT' +
'PIVOT(SUM(Value) FOR CommonMonthDate IN(' + @cols + ')) AS PVT'
EXEC(@qry)
Note: Please, change table aliases (j or r) respectively!
I did use smart trick from:
sqlauthority.com: SQL SERVER – Script to Find First Day of Current Month[
^] to convert each date into common form: first day in month to bea able to pivot data in monthly manner.