I have a single table of payments called PYMT, and am trying to wrap my head around using a PIVOT if possible to get a certain arrangement for an output and befuddled as how to do this. In the table are pymt_amount and pymt_date (other columns too, but not necessary). I wish to see the output to look like so:
PayMonth 2007 2008 2009 2010 2011
1 26044.12 82663.79 83583.17 35963.49 100865.94
2 60145.61 35245.06 19173.08 14417.98 21502.71
3 68138.88 88670.16 85319.66 40850.39 31595.43
4 228835.04 215258.84 157905.56 136551.46 166027.30
5 395877.88 348307.58 348506.09 363460.24 298488.22
6 618013.05 662869.88 522233.48 472174.95 385879.94
7 557751.27 363659.66 305363.68 304606.98 349173.75
8 355639.91 173107.60 266235.54 147731.54 251878.49
9 131440.63 173338.90 133869.36 140035.13 109595.83
10 168148.90 127356.25 114818.69 119082.52 139201.50
11 139543.35 138151.22 128667.58 137351.77 107807.27
12 142286.06 136670.64 116980.04 69609.22 85670.84
To get the first column of payment totals is easy - it's doing it for the other years that I can't figure out - I know how to do a basic PIVOT table.
The query for the first 2 columns is
SELECT DATEPART(MM, pymt_Date) AS PayMonth, SUM(pymt_Amount) AS [2007]
FROM PYMT
GROUP BY DATEPART(MM, pymt_Date) , DATEPART(YY, pymt_Date)
HAVING (DATEPART(YY, pymt_Date) = 2007)
ORDER BY PayMonth
How to add the other years (each payMonth is a sum of all payments for the month), but I don't wish to pivot by month (just the years by the month as I show in the output)
I could run a separate query in a cursor per month Here's an example, but showing the grand total for the year (but it needs to be separated by month)
SELECT * FROM
(SELECT DATEPART(yy, pymt_Date) AS PayYear, pymt_Amount
FROM PYMT
) tbl1
PIVOT
(SUM(pymt_Amount)
FOR
PayYear in ([2007],[2008],[2009],[2010],[2011])
) tbl2
which yields
2007 2008 2009 2010 2011
2891864.70 2545299.58 2282655.93 1981835.67 2047687.22
As you can see - this isn't broken down by month rows
Any ideas?
CodePudding user response:
You missed this DATEPART(month, pymt_Date) AS PayMonth
in the tbl1 query.
The complete query should be
SELECT *
FROM
(
SELECT DATEPART(year, pymt_Date) AS PayYear,
DATEPART(month, pymt_Date) AS PayMonth,
pymt_Amount
FROM PYMT
) tbl1
PIVOT
(
SUM(pymt_Amount)
FOR PayYear in ([2007],[2008],[2009],[2010],[2011])
) tbl2