CODEWITHSUNDEEP
sqlsql-servert-sqlgroup-bypivot
David
David

Reputation: 279

Query to "PIVOT" the results of SalesAmount & CumulativeMonthly for specific YEARS

I have a database table that captures every Sales Transaction:

Transactions 
(
    ID INT, 
    TransactionDate DATETIME, 
    SalesAmount MONEY
)

I need to run a T-SQL query to produce a Sales Report, that shows :

(1) Sales (monthly) (2) Cumulative Monthly Sales

These will be two columns per year; PIVOT the results for next year on next two neighbor columns (structure as ExcelSheet template below). The results will extend horizontally for all Years in the transaction data.

enter image description here

Upvotes: 0

Views: 93

Answers (1)

GMB
GMB

Reputation: 222702

You could use conditional aggegation and window functions, as follows:

select
    datename(month, TransactionDate),
    sum(case when year(TransactionDate) = 2017 then SalesAmount end) [Sales 2017],
    sum(sum(case when year(TransactionDate) = 2017 then SalesAmount end))
        over(order by month(TransactionDate)) [Cumulative 2017],
    sum(case when year(TransactionDate) = 2018 then SalesAmount end) [Sales 2018],
    sum(sum(case when year(TransactionDate) = 2018 then SalesAmount end))
        over(order by month(TransactionDate)) [Cumulative 2018],    
    sum(case when year(TransactionDate) = 2019 then SalesAmount end) [Sales 2019],
    sum(sum(case when year(TransactionDate) = 2019 then SalesAmount end))
        over(order by month(TransactionDate)) [Cumulative 2019],        
from Transactions
group by month(TransactionDate), datename(month, TransactionDate)
order by month(TransactionDate)

Upvotes: 2

Related Questions