How to arrange output by months pivoted by year in SQL?-CodePudding

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