SQL Server PIVOT by multiple columns

Question

I already search for multiple pivots but still I haven't found similar to mine. I am using SQL Server 2008.

I already created a query with pivot:

select * 
from 
    (select 
         branch, sum(balance) [balance], 
         year(docdate) [year], month(docdate) [month]
     from 
         tempprt 
     group by 
         branch, year(docdate), month(docdate)) as a
pivot 
    (sum(balance) 
     for month in ([1], [2])) as pvt1

and I get this output:

Now, I want to sum the columns 1 and 2 per year and make the year as columns:

Your help is appreciated. Thank you!

Does this answer your question? [Pivot multiple columns based on one column in SQL Server](https://stackoverflow.com/questions/18023479/pivot-multiple-columns-based-on-one-column-in-sql-server) — Serg, Mar 21 '20 at 07:02
Just as a note: SQL Server 2008 and 2008 R2 are **out of extended support** by now - https://www.red-gate.com/simple-talk/sql/database-administration/the-end-of-sql-server-2008-and-2008-r2-extended-support/ - time to upgrade! — marc_s, Mar 21 '20 at 08:41

mkRabbani · Accepted Answer · 2020-03-21T07:13:14.260

You can try this below script-

select branch,

SUM(CASE WHEN year(docdate) = 2019 THEN balance ELSE 0 END) [2019],
SUM(CASE WHEN year(docdate) = 2019 AND MONTH(docdate) = 1 THEN balance ELSE 0 END) [2019_1],
SUM(CASE WHEN year(docdate) = 2019 AND MONTH(docdate) = 2 THEN balance ELSE 0 END) [2019_2],

SUM(CASE WHEN year(docdate) = 2018 THEN balance ELSE 0 END) [2018],
SUM(CASE WHEN year(docdate) = 2018 AND MONTH(docdate) = 1 THEN balance ELSE 0 END) [2018_1],
SUM(CASE WHEN year(docdate) = 2018 AND MONTH(docdate) = 2 THEN balance ELSE 0 END) [2018_2]

from tempprt 
GROUP BY branch

SQL Server PIVOT by multiple columns

1 Answers1