CODEWITHSUNDEEP
sqlsql-serversql-server-2008pivotmultiple-columns
404notfound
404notfound

Reputation: 77

SQL Server PIVOT by multiple columns

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:

enter image description here

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

enter image description here

Your help is appreciated. Thank you!

Upvotes: 0

Views: 75

Answers (1)

mkRabbani
mkRabbani

Reputation: 16908

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

Upvotes: 3

Related Questions